Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
risabhroy_03
Partner - Creator II
Partner - Creator II

Applymap

I have a challenge that I have 2 tables TDIPU001 and TDIPU010

I have to pick supplier based on the following condition

1) Take [T$ITEM] from TDIPU001 and match TDIPU001.[T$ITEM] with TDIPU010.[T$ITEM]

2) If [OFBP] is found in TDIPU010 then pick it from there and name it as Supplier else pick from TDIPU001.[T$OFBP]

Below is my code but some items are missing - 

 

[Map_Supplier]:
Mapping
Load
"T$ITEM" &'|'& Company_Code as [Temp_Key_OTBP],
"T$OTBP" as [OTBP_010]
FROM [$(vSource)\TTDIPU010.qvd](qvd)
where ("T$IBPS"=1) and ((floor(num("T$EFDT")))<='$(vToday)') and (((floor(num("T$EXDT")))>='$(vToday)') or ((Date("T$EXDT"))='1/1/1970'));

 

[A_Stage 1]:

Load
Company_Code,
"T$ITEM" & '|' & Company_Code as [A_Temp_Key_Item],
"T$ITEM" as [Item],
"T$OTBP" as [Supplier_001],
FROM [$(vSource)\TTDIPU001.qvd](qvd);

 

[Stage 3]:

Load *,
ApplyMap('Map_Supplier',[Item] & '|' & Company_Code,[Supplier_001]) as [Supplier]


Resident [A_Stage 1];
Drop Table [A_Stage 1];
Exit Script;

 

Labels (1)
1 Reply
Angela_Zhou
Contributor III
Contributor III

If I understand right, in your [Stage 3] when you load data from "TTDIPU001", you are trying define [Supplier] as,
IF found matched key:
     TTDIPU001.[A_Temp_Key_Item] = [Map_Supplier].[Temp_Key_OTBP]
Then Use:
     [Map_Supplier].[OTBP_010//T$OTBP from TTDIPU010
Else Use:
      [Supplier_001]                            //TDIPU001.[T$OTBP]

If above assumption is correct, since your description seems messed up on [T$OFBP] & [T$OTBP]), you can try below:

[Stage 3]:
Load *,
ApplyMap('Map_Supplier', [A_Temp_Key_Item], [Supplier_001]) AS [Supplier],

//to debug key missing in the map,
//default value = Null() to ensure Len(Null) returns zero for key not found
IfLENApplyMap('Map_Supplier', [A_Temp_Key_Item], null()) )>0, "In", "Not") AS isKeyInMap

Resident [A_Stage 1];
Drop Table [A_Stage 1];
Exit Script;

Angela Z.