Load editor issue

[My_Rebecca]

My expectation:

1) both sheets contain "Sales Order" & "Item" with different titles.

2) combine "Sales Order" & "Item" in both sheets as searching K value

3) use ApplyMap to quote the expected data from one sheet to another.

Error Notic:

Script:

TJInventoryMap1:
Mapping Load
"Sales Order"&'-'&"Sales order item" as "DC TJ GR SO & SO Item",
"Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Load
"Sales document"&'-'&"Sales Document Item" as "DC TJ Inventory SO & SO Item",
"DC TJ Inventory SO & SO Item" as "DC TJ GR SO & SO Item",
Material,
Plant,
"Special Stock",
"Base Unit of Measure",
Unrestricted,
Currency,
"Value Unrestricted",
"Sales document",
"Sales Document Item",
Vendor,
"Purchasing Document",
Item,
"Profit Center",
"Profit Center Name",
"Amount LC per unit",
Currency1,
"Descr. of Storage Loc.",
"Storage Location",
ApplyMap('TJInventoryMap1',"DC TJ GR SO & SO Item",null()) as "Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_20230217.XLSX]
(ooxml, embedded labels, table is Sheet1);

[tresesco · Accepted solution]

Tty using the calculation instead of the newly generated field in applymap(), like:

ApplyMap('TJInventoryMap1', "Sales document"&'-'&"Sales Document Item" ,null()) as "Posting Date"

[My_Rebecca]

Error remains as before.

[tresesco]

You have to make the similar correction, in all the places where you referred the same field in the same load statement where you are creating it. Like:

"DC TJ Inventory SO & SO Item" as "DC TJ GR SO & SO Item", should be corrected as

 "Sales document"&'-'&"Sales Document Item" as "DC TJ GR SO & SO Item",

[My_Rebecca]

Error again:

TJInventoryMap1:
Mapping Load
"Sales Order"&'-'&"Sales order item" as "DC TJ GR SO & SO Item",
"Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Load
"Sales document"&'-'&"Sales Document Item" as "DC TJ GR SO & SO Item",
Material,
Plant,
"Special Stock",
"Base Unit of Measure",
Unrestricted,
Currency,
"Value Unrestricted",
"Sales document",
"Sales Document Item",
Vendor,
"Purchasing Document",
Item,
"Profit Center",
"Profit Center Name",
"Amount LC per unit",
Currency1,
"Descr. of Storage Loc.",
"Storage Location",
ApplyMap('TJInventoryMap1',"DC TJ GR SO & SO Item",null()) as "Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_20230217.XLSX]
(ooxml, embedded labels, table is Sheet1);

[tresesco]

You didn't make correction in applymap()

[MayilVahanan · Accepted solution]

Hi

Try like below, 

TJInventoryMap1:
Mapping Load
"Sales Order"&'-'&"Sales order item" as "DC TJ GR SO & SO Item",
"Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Load
"Sales document"&'-'&"Sales Document Item" as "DC TJ GR SO & SO Item", //new field name 
Material,
Plant,
"Special Stock",
"Base Unit of Measure",
Unrestricted,
Currency,
"Value Unrestricted",
"Sales document",
"Sales Document Item",
Vendor,
"Purchasing Document",
Item,
"Profit Center",
"Profit Center Name",
"Amount LC per unit",
Currency1,
"Descr. of Storage Loc.",
"Storage Location",
ApplyMap('TJInventoryMap1',"Sales document"&'-'&"Sales Document Item" ,null()) as "Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_20230217.XLSX]
(ooxml, embedded labels, table is Sheet1);

New field name can't be use in the same load.. Either you can use in Preceding load or Resident load or in peek function.

In your case, in Apply map statement, instead of using "New Field name", try to use the existing fields. 

This is @tresesco informed previously also. 

[My_Rebecca]

@MayilVahanan  @tresesco 

Thanks both. I believe your comment is correct. However I meet another question during loading, which happens many times before. 

As a matter of fact, "Table 'Sheet1' not found" happened many times beofre as I load different datasource.

Why does it happen? I'm not sure whether it comes from Auto-generated section.

Could you please help me to identify the cause? many thanks.

TJInventoryMap1:
Mapping Load
"Sales Order"&'-'&"Sales order item" as "DC TJ GR SO & SO Item",
"Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Load
"Sales document"&'-'&"Sales Document Item" as "DC TJ Inventory SO & SO Item",
Material,
Plant,
"Special Stock",
"Base Unit of Measure",
Unrestricted,
Currency,
"Value Unrestricted",
"Sales document",
"Sales Document Item",
Vendor,
"Purchasing Document",
Item,
"Profit Center",
"Profit Center Name",
"Amount LC per unit",
Currency1,
"Descr. of Storage Loc.",
"Storage Location",
ApplyMap('TJInventoryMap1',"Sales document"&'-'&"Sales Document Item" ,null()) as "Posting Date"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_20230217.XLSX]
(ooxml, embedded labels, table is Sheet1);

[MayilVahanan]

Hi

if its not required, you can delete the Autogenerate code ..

Or you can use Exit script; 

[My_Rebecca]

From your words, I know furtherly about how Data Manger generates and how it disappears. Many thanks.