Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Derive a field by checking on a condition from related tables

i have one fact table and various Dimension tables linked by ID.

I need to derive a new column in fact table based on the values from different dimension tables.

if the corresponding id is not present in the Dimension table then put a default value in the derived Column.

Can someone help me find the solution for the above scenario.

Regards.

3 Replies
albertovarela
Partner - Specialist
Partner - Specialist

One way would be to create/use a mapping table.

For example:

After loading your Dimension_Table_1 perform a Mapping Load with using the ID and "Dimension A":

DimensionA_Map:

Mapping LOAD

     ID,

     [Dimension A]

Resident Dimension_Table_1;


Then while loading your Fact apply the map


Fact:

LOAD

     ID,

     ApplyMap('DimensionA_Map', ID, 'default value') as [Dimension A in Fact table]  //If ID doesn't exist in the Map it will populate the value in the 3rd param

From

     YourFactTable

;

  

For more info about the Apply map see

Don't join - use Applymap instead

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/MappingFunctions/ApplyM...

sasikanth
Master
Master

Hi,

Please elaborate your requirement a bit more i.e. what exactly you want to derive??.

You can use exists function to check the same but that depends on the logic you want to apply.

Try this:

Load All Dimension tables first then,


FactTable:

Load  *,

IF(Exists(Dim_ID), yourLogic, 'Default value' ) as DerivedColumn

From FactTable ;

thanks,

Anonymous
Not applicable
Author

Hi Sasi,

Please refer to the below link for the actual requirement.

Re: How to create a data set.

Regards

Shiva