topic Link table or Concatenate in QlikView

Link table or Concatenate

qliktech_uk — Sun, 14 May 2017 10:40:56 GMT

Hi,

I have a scenario where I have 2 KPIs, both of them derived using separate queries,

The combination of Key1 and Key2 would be the primary key.

Now I need to join Query 1 and Query 2 with other queries, 10 of them. 10 needs to be joined with either KPIs.

Whats the best way to create the data model?

Anil_Babu_Samineni — Mon, 15 May 2017 02:06:55 GMT

May be we can follow many ways, I am assuming

Scenario-1

Load *, Key1 & Key2 as PrimaryKey from Query1;

Concatenate

Load *, Key1 & Key2 as PrimaryKey from Query2;

Scenario-2

Query1:

Load *, AutoNumberHash128(Key1, Key2) as PrimaryKey;

Load Year, Month, Key1, Key2, KPI1 from Query1;

Query2:

Load *, AutoNumberHash128(Key1, Key2) as PrimaryKey;

Load Year, Month, Key1, Key2, KPI1 from Query2;

Scenario-3

Query1:

Load Year, Month, Key1, Key2, KPI1, 'Query1' as Flag from Query1;

Query2:

Load Year, Month, Key1, Key2, KPI1, 'Query2' as Flag from Query1;

Or, Link table also easy way

qliktech_uk — Mon, 15 May 2017 13:16:50 GMT

Thanks Anil. How can I use a link table here?

Anil_Babu_Samineni — Tue, 16 May 2017 06:00:47 GMT

Hope this helps and if you got answer please close this thread

Query1:

Load *, Key1 & Key2 as PrimaryKey from Query1;

Query2:

Load *, Key1 & Key2 as PrimaryKey from Query2;

LinkTable:

Load DISTINCT *, PrimaryKey Resident Query1;

Concatenate(LinkTable)

Load DISTINCT *, PrimaryKey Resident Query2;

Drop fields Key1, Key2 from Query1, Query2;