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

[rubenmarin1]

Hi, the SAP table doesn't have a From/Resident sentence that tells the source of the data, so there is no loaded data, and that's why it tells that the table doesn't exists.

I've checked the files attached and still I don't know which should be the source of data. Check again trying to fill the mapping table with some rows of data.

[cristianj23a]

SAP:
Mapping Load
"TradingCo1",
"SAPSFMSstatusSalesorder"

FROM [Path of your SAP connector];

In your mapping you close with a semicolon but before that you must put the path of your files or connector where you are extracting your data so that qlik identifies the location.

Regarts.

[KirstenKa]

Thanks both @rubenmarin1  and @cristianj23a  I understand the issue that occurred. Though from the example here in Mapping Load  I understand that the value in which you need to join with, need to have identical names, in my example they have different names ("Trading Co" and "sku") , and therefore I renamed both values in both datasources to 1 name ->TradingCo1. Though when I add the original datasource (FROM [lib: ) it doesn't recognize the transformed code names, which is logical because there is no transformation there. So is there a way that I can create a new datasource in the script which includes the transformations, and to which I can refer to in my mapping code. Or is there a way in the mapping code where I can use 2 different value names for the join? 

Also with 'SAP'in yellow marked  I am most propably not referring to the SAP datasource, I wanted to refer to. I thought in the code you can just refer to the purple word, so I wanted to refer to the purple word

[rubenmarin1]

Hi, the mapping sentence is still incomplete, it needs the source of data (From/resident/inline).

mapSAP:
Mapping load
  Field1,
  Field2
Resident SAP;

And if there is already a SAP table you need to set a different name for the mapping table (I usually use 'map' prefix to name a mapping table.

To add more rows to the mapping table you can add another sentence. In example, if you want to addd the rows from the C8 table:

mapSAP:
Mapping load
  Field1,
  Field2
Resident C8;

[KirstenKa]

@rubenmarin1 When I use the resident it says the resident is not found

This is the file I am referring to in my resident

Outer Join( C8 )

SAP:

LOAD

   "Item ID" & '|' & Num( "Color" , '000' )as %ItemColor_GlobalId,

    "Item ID" & '|' & "Color"  as ItemColor_GlobalId_SAP,

   "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);

This is the other dataload related to the mapping code, which is in my code above the SAP load:

tmp_C8:

LOAD

    "Item Trading Code" & '|' & "Color Code" as %ItemColor_GlobalId,

    "Item Trading Code" & '|' & "Color Code"  as ItemColor_GlobalId_C8,

    'FT' as _indSection,

    "Item Trading Code" ,

     "Color Code"

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');

Concatenate( tmp_C8 )

LOAD

    "Item Trading Code" & '|' & "Color Code" as %ItemColor_GlobalId,

    "Item Trading Code" & '|' & "Color Code" as ItemColor_GlobalId_C8,

    'APP' as _indSection,

     "Item Trading Code" ,

     "Color Code"

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 Region Total Season Qty" >0);

Qualify *;

Unqualify '%*';

Unqualify '_*';

NoConcatenate

C8:

Load

*

Resident tmp_C8

;

And this is my mapping code code 

[rubenmarin1]

Hi @KirstenKa , resident should use a table already laoded in the model, you are loading SAP table as a join in another table, so 2 options:

- Use the table of the join (C8)

- Load SAP table as a independent and temproary table, do the mapping using this table, and then drop the SAP table.

[KirstenKa]

@rubenmarin1  Using the table of the join doesn't work, I used  Resident tmp_C8 for my Sap mapping but then it doesn't recognize the SAP fields, Should I used another table of the join (C8 value)  for the resident?

I also first loaded the SAP table independently, then did the mapping as in previous example, after that Drop table SAP; After that the join again, but also then it doesn't recognize the field in SAP 

[rubenmarin1]

The steps are:

1. load the sap table

2 create the mapping table using the sap table

3. drop the sap table.

If there are no transformations you can do the load using directly the source of the sap table:

mapSap:
Mapping LOAD
   "Item ID",
   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);

[KirstenKa]

Thanks @rubenmarin1  it at least runs without errors. Though it does not give me the result I need in the data. There are a few  challenges/questions

• Do I need to do the mapping after the join or before the join? In which order join vs mapping?
• I need to use the second C8 mapping table, with applymap, from the concatenated (joined) C8 data, however now since I use the individual datatable with FROM I can not use the joined data. I now created 2 times an applymap code but don't think this is the right way
• In the first mapping code, while loading it says it only expects 2 values, and I have 3: Item ID, Color and SAPSFMSstatusSalesorder. I fixed the issue of 2 values by using "Item ID and Color" but don't think this is right-> I need the combination of Item ID and Color to create a unique code where I can match with in the second mapping code: "Item Trading Code and Color Code"
• In the sample they used an additional value "sales person" for the 2nd mapping code. I don't use it I only need the combination of "Item Trading Code and Color Code" Not sure if this is causing a problem
• The  Drop table mapSap; and Drop table mapC8FT; don't work when I run the script
  
  

This is the code I created

mapSap:
Mapping LOAD
   "Item ID" and "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: 
Load
"Item Trading Code",
"Color Code",
ApplyMap('mapSap',  "Item Trading Code" and "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: 
Load
"Item Trading Code",
"Color Code",
ApplyMap('mapSap',  "Item Trading Code" and "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 Region Total Season Qty" >0);