Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
sarunome
New Contributor

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

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.

5 Replies
Highlighted
agni_gold
Valued Contributor III

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

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

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

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

 

Highlighted
sarunome
New Contributor

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


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

Highlighted
agni_gold
Valued Contributor III

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

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

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


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