topic Re: Linking data from 2 systems in QlikView

Linking data from 2 systems

burgersurfer — Fri, 20 Oct 2017 11:11:45 GMT

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

Re: Linking data from 2 systems

cbaqir — Fri, 20 Oct 2017 14:19:25 GMT

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.

Re: Linking data from 2 systems

burgersurfer — Mon, 23 Oct 2017 19:38:33 GMT

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

Re: Linking data from 2 systems

cbaqir — Mon, 23 Oct 2017 19:41:35 GMT

Can you post a sample QVW and data file?

Re: Linking data from 2 systems

cbaqir — Mon, 23 Oct 2017 19:44:12 GMT

What is the end result you are trying to accomplish?

Re: Linking data from 2 systems

burgersurfer — Mon, 23 Oct 2017 20:12:20 GMT

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

Re: Linking data from 2 systems

burgersurfer — Mon, 23 Oct 2017 21:12:31 GMT

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!

Re: Linking data from 2 systems

qliksus — Wed, 25 Oct 2017 08:57:44 GMT

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

Re: Linking data from 2 systems

burgersurfer — Thu, 26 Oct 2017 10:05:15 GMT

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

Corne