Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

burgersurfer
New Contributor II

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
cbaqirdhds
Contributor II

Re: Linking data from 2 systems

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
New Contributor II

Re: Linking data from 2 systems

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

cbaqirdhds
Contributor II

Re: Linking data from 2 systems

Can you post a sample QVW and data file?

cbaqirdhds
Contributor II

Re: Linking data from 2 systems

What is the end result you are trying to accomplish?

burgersurfer
New Contributor II

Re: Linking data from 2 systems

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
New Contributor II

Re: Linking data from 2 systems

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
Valued Contributor

Re: Linking data from 2 systems

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
New Contributor II

Re: Linking data from 2 systems

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

Corne