Need work around to populate 1:M values in Apply map and populate as ID instead on Unknown

[Anonymous]

Hi ,

I have 2  tables like belo w:

Mapping:                                                                                     

comp_key  ,  Value 

01-r01,v01

02-r02,v02

Main Table:

Comp_key,Value,amount

01-r01, , 100

01-r01,,120

01-r01,,135

03-r03,v05,125

I have to do the apply map in the Main table when Value is null or blank.

LOAD 

IF(Value='' or ISNULL(Value),APPLYMAP('Mapping','comp_key','Unknown') AS Value,

Comp_Key,

Amount;

Out come is like that:

Comp_key,Value,amount

01-r01, Unknown, 100

01-r01,Unknown,120

01-r01,Unknown,135

03-r03,v05,125

please suggest a way to populate the values like that;

01-r01, v01, 100

01-r01,v01,120

01-r01,v01,135

03-r03,v05,125

please suggest...

[Gysbert_Wassenaar]

The field name comp_key should not be in single quotes. In single quotes it's a literal string, not the value of the field that will be looked up in the mapping table. Try:

IF(Value='' or ISNULL(Value),APPLYMAP('Mapping', [comp_key],'Unknown') AS Value,

[MindaugasBacius]

I suggest to change this script sentence:

IF(Value='' or ISNULL(Value),APPLYMAP('Mapping','comp_key','Unknown') AS Value

Into this:

IF(Len(Trim(Value))=0, APPLYMAP('Mapping', comp_key, 'Unknown')) AS Value

Read this short help file to understand the syntax of ApplyMap:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/MappingFunctions/Apply...