Applymap

[risabhroy_03]

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;

[Angela_Zhou]

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
If( LEN( ApplyMap('Map_Supplier', [A_Temp_Key_Item], null()) )>0, "In", "Not") AS isKeyInMap

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