Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
burgersurfer
Contributor III
Contributor III

Linking data from 2 systems

Hi all

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

C

Table design joins.jpgTable design Door and Store.jpg

8 Replies
cbaqir
Specialist II
Specialist II

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.

burgersurfer
Contributor III
Contributor III
Author

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?

Corne

cbaqir
Specialist II
Specialist II

Can you post a sample QVW and data file?

cbaqir
Specialist II
Specialist II

What is the end result you are trying to accomplish?

burgersurfer
Contributor III
Contributor III
Author

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

Corne

burgersurfer
Contributor III
Contributor III
Author

Hi

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!

qliksus
Specialist II
Specialist II

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

from  DATA_DAY_Equipment

join

load Doorid , Store_no

from STORE_XT

burgersurfer
Contributor III
Contributor III
Author

Thank you Susant, that is effectively what we did by adding the store_no field into the Data_Day_Equipment table

Corne