Associate issue

[My_Rebecca]

The matching columns are "Material Document" and "GR Number" in two worksheets.

Now I want to create the new column E in first sheet to add "Truck Arrival Time" as following. 

I tried the "Associate" in Data Manager without success.

How to realize it in Data Manager? or Data Load Editor? Better 2 solutions are expected, thanks

[sidhiq91]

@My_Rebecca  Please see the code that can be used in the script editor.

Noconcatenate

Temp:

load [Material Document] as [GR Number],

        Items,

      Material,

     [Material Description]

From Table1;

Left join (Temp)

Load [GR Number],

          [Truck Arrival Time]

From table2;

Exit Script;

If this resolves your issue, please like and accept it as a solution

[My_Rebecca]

@sidhiq91 

Dear @sidhiq91 ,

please refer to following error, could you please help to correct it? thanks.

LOAD
Plant,
"Storage Location",
"Movement Type",
"Material Document" as "GR Number",
"Material Doc.Item",
"Posting Date",
"Time of Entry",
"Special Stock",
Supplier,
Material,
"Material Description",
Quantity,
"Unit of Entry",
"Amount in LC",
"Purchase Order",
Item,
"Sales Order",
"Sales order item"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR*.XLSX]
(ooxml, embedded labels, table is Sheet1);
left Join
Load
"GR Number",
"Truck arrival"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/3PL/inbound report*.XLSX]
(ooxml, embedded labels, table is PTO);

[sidhiq91]

@My_Rebecca  Could you please share the sample file from both excel sheet. As far I see only if the field is missing or if the field is misspelled then we get such error.

[My_Rebecca]

dear @sidhiq91 ,

please refer to enclosed file, thanks.

[My_Rebecca]

Is there any other solution? thanks.

[MayilVahanan]

Hi

May be, one of the file doesn't contain the "Material Document" field.

 [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR*.XLSX]

And also, while joining, use distinct

Load Distinct
"GR Number",
"Truck arrival"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/3PL/inbound report*.XLSX]
(ooxml, embedded labels, table is PTO);

[My_Rebecca]

@MayilVahanan 

Dear,

The column "Material Document" does exist in the data source. If I load the 1st sheet separately, it works.

[MayilVahanan]

Yes,

However, you're loading many files at a time by using "*"

inbound report*.XLSX

Please verify the files.. Hope one or 2 files doesn't have "Material Document"..

[My_Rebecca]

dear,

I try to load with half success. The GR number is loaded and created in the first sheet, however the Truck arrival time is null.  

"inbound report*" has duplicated GR number, each GR number has different Truck arrival time. I suppose the error occurs due to this.

So I think if unduplicated GR number & corresponding first Truck arrival line could be selected, the association job could be run successfully. Could you help to realize it? thanks.