Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qliktech_uk
New Contributor III

Link table or Concatenate

Hi,

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

Query1Query2
YearYear
MonthMonth
Key1Key1
Key2Key2
KPI1KPI2

   

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?

Tags (1)
3 Replies

Re: Link table or Concatenate

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

Re: Link table or Concatenate

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

Re: Link table or Concatenate

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;

Community Browser