Derive a field by checking on a condition from related tables

[Anonymous]

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.

[albertovarela]

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]

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]

Hi Sasi,

Please refer to the below link for the actual requirement.

Re: How to create a data set.

Regards

Shiva