Add Vlookup result to column (complicated issue)

[KirstenKa]

See the first attachment  "Mapping1" with an explanation of what I would like to accomplish

From an earlier post I understood I need to use Mapping Load  (click on link for explanation)

I used the formula from Mapping Load like below. And see in the third attachment "Mapping-explanation code"

And scroll further below to see the error message

[rubenmarin · Accepted solution]

Hi,

• You can't drop the mapping tables, they are automatically dropped at the end
• To create a mapping table you need to use the 'Mapping' sentence, so mapc8FT and mapc8App are no mapping tables, just regular tables.
• To add strings you need to use &, not and: "Item ID" & "Color" as keyItemColor
• If you load 2 tables with the same names they are autoconcatenated, meaning that the 2nd table adds rows to the previous tables instead of creating a new one. If you want an independent table use 'NoConcatenate'; NoConcatenate LOAD ...
• About the other: a mapping table is just a table with 2 columns (that matters) the first is the value to search, and the 2nd the value that returns. Then use applymap on other tables to do the needed transformations.
• On unexpected results try to load as a regular table and check the values loaded, maybe the format or the values have something different than the values used in the table with applymap.

mapSap:
Mapping LOAD
   "Item ID" & "Color",
      If(num#([MovAv.4200],'#,00')<>0 AND num#("LSRP-4000",'#,00')<>0 AND trim([MovAv.4200]) & ''<>'' AND 
   ("LSRP-4000")<>0 AND ([MovAv.4200])<>0 AND len(trim(Theme))*len(trim(MovAv.4200))*len(trim("CoO(Purcha"))
   *len(trim("LSRP-4000")), 'Ready', 'Not ready')  as SAPSFMSstatusSalesorder
   
   FROM [lib://QlikStorage/1. Development/Private Data/0. Data/1. User Input/ArticleStatusReport.xlsx]
(ooxml, embedded labels, table is Sheet1);


mapc8FT: 
NoConcatenate Load
"Item Trading Code",
"Color Code",
ApplyMap('mapSap',  "Item Trading Code" & "Color Code",'Not ready') 
as  SAPSFMSstatusSalesorder
FROM [lib://QlikStorage/1. Development/Private Data/0. Data/1. User Input/ArticleStatusReport.xlsx]
(ooxml, embedded labels, table is LinePLan) WHERE (Region='AEG'AND "LP03 AEG Adopted"='true');


mapc8App: 
NoConcatenate Load
"Item Trading Code",
"Color Code",
ApplyMap('mapSap',  "Item Trading Code" &"Color Code",'Not ready') 
as  SAPSFMSstatusSalesorder
FROM [lib://QlikStorage/1. Develo

[KirstenKa]

Thanks for the good explanation @rubenmarin  the code works without errors however it doesn't give the desired result. Column T should have the "Not ready" value, and it should have because when there is a value in column B and no value in A it should give "Not ready" in column T

[rubenmarin]

Try the last point of my previous post:

• On unexpected results try to load as a regular table and check the values loaded, maybe the format or the values have something different than the values used in the table with applymap.

If it's not returning 'Not ready' it means theat the value to search is being loaded in the mapping table. 

You can try adding an additional condition when loading from excel like "and len(trim("Color"))>1"

[KirstenKa]

@rubenmarin  ah ok it could be related to that some values do not join properly due to removed leading zero's in on column, which I first need to fix

Where should I add this: "You can try adding an additional condition when loading from excel like "and len(trim("Color"))>1" in the load code or the mapping code? Aand how can I add leading zero's to my value? In some cases I have 2 zero's (002)  but when there is a 1 there are 3 zero's (0001) to make it more complicated

[rubenmarin]

Hi, it looks like it should go on the mapping table, but I don't know your data, think that you need to create a table where you want to search for the first column, and return the second column of that row, like a simpler version of vlookup, fixed to 2 columns. The mapping table has to have that format.

[KirstenKa]

Hi @rubenmarin  @cristianj23a  the issue was eventualy resolved with an if statement instead of mapping load. Thanks for thinking with me!

Finaltable:
Load
	*,
    If(Trim( "C8_SS24.Item Trading Code" & "C8_SS24.Color Code")&'' <> '' and Trim( "SAP_SS24.Item ID" & "SAP_SS24.Color") &  ''=''
    	,'Not ready'
     	, If(num#([SAP_SS24.MovAv.4200],'#,00')<>0 AND num#("SAP_SS24.LSRP-4000",'#,00')<>0 AND trim([SAP_SS24.MovAv.4200]) & ''<>'' AND ("SAP_SS24.LSRP-4000")<>0 AND ([SAP_SS24.MovAv.4200])<>0 AND len(trim(SAP_SS24.Theme))*len(trim(SAP_SS24.MovAv.4200))*len(trim("SAP_SS24.CoO(Purcha"))*len(trim("SAP_SS24.LSRP-4000")), 'Ready', 'Not ready') 
	 ) as SAPFMSstatusSalesorder
	
Resident C8_SS24;

Drop table C8_SS24;