Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Left Join:
Join:
how to improve? thanks.
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;
thanks, but it yet does not work. how to improve the "Temp"?
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
Sorry, I'm new user in Qlik Sense, so I could not understand the correction method.
Could you paste your code here?
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
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
Seems to close to workable:
Oops! Missed the semi-colon ..
Drop table Temp;