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

[My_Rebecca]

Left Join:

Join:

how to improve? thanks.

[MayilVahanan]

Hi

In that case, you can use either min or max or firstvalue or lastvalue information like below

Temp:

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

Left join(urfirsttablename) //where your "DC TJ GR*.XLSX" file loaded

Load 
"GR Number",
Min("Truck arrival") as "Truck arrival"
Resident Temp

group by "GR Number";

Drop table Temp;

[My_Rebecca]

thanks, but it yet does not work. how to improve the "Temp"? 

[MayilVahanan]

Hi 

You need to mention the table name infront of load statement. In your scenario, you have 3 load.

Table1:

Load "Material Document" ...;

Temp:

Load Distinct "GR Number" ,...;

Left join(Table1)

Load "GR Number", Min().. ;

Drop table Temp;

Hope it clear

[My_Rebecca]

Sorry, I'm new user in Qlik Sense, so I could not understand the correction method.

[MayilVahanan]

Could you paste your code here?

[My_Rebecca]

LOAD "Material Document" as "GR Number",
Plant,
"Storage Location",
"Movement Type",
"Material Document",
"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 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);

Load
"GR Number",
Min("Truck arrival") as "Truck arrival"
Resident Temp
group by "GR Number";
Drop table Temp

[MayilVahanan]

Try like below ... Happy learning!!

Table1:

LOAD "Material Document" as "GR Number",
Plant,
"Storage Location",
"Movement Type",
"Material Document",
"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);

Temp:
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);

Left Join(Table1)
Load
"GR Number",
Min("Truck arrival") as "Truck arrival"
Resident Temp
group by "GR Number";

Drop table Temp

[My_Rebecca]

Seems to close to workable:

The following error occurred:

Unexpected token: 'LOAD', expected: ','

The error occurred here:

Drop table Temp ///$tab DC TJ GR Truck Arrival Time >>>>>>LOAD<<<<<< "TO Number", "Created On", "Time", Conf.Date, Conf.t., "GR Number", Material, "Dest. Bin", "Source bin", qty, "Material Description", User, "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)

[MayilVahanan]

Oops! Missed the semi-colon ..

Drop table Temp;