I am trying to link data from 2 systems into a model, but have difficulty in getting the level of detail right.
Two systems - one a Point-of-sale system and the other a traffic counter. In the POS system, all transaction information is stored on a store level. For the other system, data is stored on a door level - e.g. a retail store may have multiple entrances, and we receive the traffic flow information per entrance (door). All events are obviously time-based, which means the information all links back to the same calendar.
We tried using two approaches as indicated in the table layouts, to no success. Please assist with comments on what we are doing wrong?
"Table design joins.bmp" (first layout below) shows how we attempted to add the higher-level store info (Store_NO) into the traffic flow info - we tried to add the Store_NO field into the DATA_DAY_EQUIP table, through which QV created its synthetic key table, but we do not match to source data
The second layout shows the issue where the data is looped through the Stores_XT, Transaction and Synthetic Key tables.
Thanks in advance
Is the goal to remove the synthetic keys? You might want to consider creating a concatenated key to make a unique join between two tables. For example, STORE_ID & '-' & DOOR_ID as %KEY_STORE_DOOR.
Thanks Cassandra, that might work.
I know how to join data from source tables into the target tables, but not how to script it from an existing QV table. The Store_ID we show in the Stores_XT table is joined to the Door_ID from different tables in source. What would the script be, to do this addition to Data_Day_Equipment? Even if we can just add the Store_No it might already solve the issue?
Hi, my end result is to link the data that exists on two differrent levels of detail, without any loops. I am happy for QV to develop Syn keys to get the results, not too much of a worry in that.
Datasets are quite large, I will try to minimise and send sample data, else I can send the QVW on its own
Thanks so much
It looks like this is resolved by using a Resident load on the existing table in QV.
I will do more checking to confirm
Thank you for the assistance, it pushed me in the right direction!
why don't you can make the STORE_XT and DATA_DAY_Equipment into one table like the below which would cutoff the loop
load Doorid , Calendar
load Doorid , Store_no