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

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

sarunome — Fri, 25 Jan 2019 13:47:28 GMT

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.

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

agni_gold — Fri, 25 Jan 2019 14:22:38 GMT

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

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

devendra_s_baba — Fri, 25 Jan 2019 15:52:24 GMT

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

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

sarunome — Mon, 28 Jan 2019 08:09:06 GMT

@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.

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

agni_gold — Mon, 28 Jan 2019 08:21:59 GMT

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

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

sarunome — Mon, 28 Jan 2019 08:23:13 GMT

@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?