Link table or Concatenate

[qliktech_uk]

Hi,

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

Query1	Query2
Year	Year
Month	Month
Key1	Key1
Key2	Key2
KPI1	KPI2

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]

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]

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

[Anil_Babu_Samineni]

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;