Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
shiva_sr
Contributor II
Contributor II

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
AlbertoV
Partner
Partner

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,

shiva_sr
Contributor II
Contributor II
Author

Hi Sasi,

Please refer to the below link for the actual requirement.

Re: How to create a data set.

Regards

Shiva