QLIK SENSE how to create one master dimension with a reference to 2 other table field values

[sarunome]

Hi,

Situation:

I have one fact table called FACT that contains fields KPI_ID, FINROW_ID, VALUE1, VALUE2. VALUE1 and VALUE2 fields have values for each row in the table, but there are no rows in the table that have values for both KPI_ID and FINDROW_ID fields. Then there are 2 other tables called KPILIST that contain fields KPI_ID, KPI_NAME, KPI_GROUP and FINSHEETLIST that contain fields FINROW_ID, FINROW_NAME, FINROW_GROUP. Key fields are underlined.

Problem:

How to create one master dimension that would contain KPI_ID and FINROW_ID but display respective values of KPI_NAME and FINROW_NAME fields that are in 2 other tables? It is possible to do that by creating a master dimension =KPI_NAME & FINROW_NAME, but then the filtering does not work for both. For example, if I select a KPI_NAME then i can not select FINROW_NAME and vice versa.

[agni_gold]

In script you can do lookup for that and make both fields in single table ?

[devendra_s_baba]

Hi Sarunome,

As per my understanding on your situation that you have mentioned.

Your both key fields KPI_ID and FINROW_ID from FACT table are not having any data and i think here is the problem. QlikView or Qlik Sense will show you excluded data (Gray Colour) if there is no linking or i will say relationship between data you are trying to slice dice.

You can use AutoGenerate() on KPI_NAME & FINROW_NAME fields to create linking records in FACT table to link other two tables.

Maybe this way your problem will solved.

Hope i have explained it good 🙂 

--

Thanks & Regards,

Devendra Babar

[sarunome]

---

@agni_gold wrote:
In script you can do lookup for that and make both fields in single table ?

---

Thank You for the reply,

This is a possible solution, but it will "explode" the data size since IDs in my particular situation have max 3 digits but NAMEs have some strings of 30+ symbols and it will replace values in millions of rows instead of ~600 throughout both of the dimensional tables.

[agni_gold]

you can go for join as well , but i would suggest , try both the way and check which takes less time .

[sarunome]

---

@devendra_s_baba wrote:

Hi Sarunome,

As per my understanding on your situation that you have mentioned.

Your both key fields KPI_ID and FINROW_ID from FACT table are not having any data and i think here is the problem. QlikView or Qlik Sense will show you excluded data (Gray Colour) if there is no linking or i will say relationship between data you are trying to slice dice.

You can use AutoGenerate() on KPI_NAME & FINROW_NAME fields to create linking records in FACT table to link other two tables.

Maybe this way your problem will solved.

Hope i have explained it good 🙂 

--

Thanks & Regards,

Devendra Babar

---

Thank You for the reply,

If there is a KPI_ID field value in FACT table, then there is  a NULL value in FINROW_ID field on the same row and vice versa. I can create another field with both values:

if(Exists(KPI_ID), KPI_ID, if(Exists(FINROW_ID), FINROW_ID, 'ERROR')) as KPIFINROWID

Then I can filter on both KPI_ID and FINROW_ID via KPIFINROWID, but still I can`t filter by the reference to their corresponding values in KPI_NAME in FINROW_NAME fields that are in KPILIST and FINSHEETLIST tables.

Is there some sort of a referal function in expression editor to link IDs to their corresponding NAME values in other tables?