Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
could you please help me figure out how to always load the last entry of a specific line (i.e. order)?
The challenge is that the number of line items per order may differ.
Please find example data attached. I have highlighted the values which I want to load.
Would be great if you could help me find an answer before this weekend.
Thanks alot in advance!
Hi,
this should work:
base:
LOAD
RowNo() as ID,
*
From [..\Qv_Load.xlsx] (ooxml, embedded labels, table is Sheet1);
left join(base)
LOAD
Max(ID) as ID,
1 as tmpLoad
Resident base
Group By Order;
Output:
LOAD
*
Resident base
Where tmpLoad = 1;
Regards
Sebastian Lettner
Use GROUP BY to group records, then use FirstSortedValue() to pick one of the grouped records. Additional field values on this particular row can also be fetched with FirstSortedValue(). Example LOAD:
Highest_Entry:
LOAD Order,
FirstSortedValue([Operation/Activity], -[Operation/Activity]) AS [Operation/Activity],
FirstSortedValue([Total order quantity], -[Operation/Activity]) AS [Total order quantity]
FROM QV_Load.xlsx (ooxml, embedded labels, table is Sheet1)
GROUP BY Order;
Best,
Peter
Hi,
this should work:
base:
LOAD
RowNo() as ID,
*
From [..\Qv_Load.xlsx] (ooxml, embedded labels, table is Sheet1);
left join(base)
LOAD
Max(ID) as ID,
1 as tmpLoad
Resident base
Group By Order;
Output:
LOAD
*
Resident base
Where tmpLoad = 1;
Regards
Sebastian Lettner
Hi Peter,
thank you!
My spreadsheet has about 43 columns. It looks like I would have to write this command for each column. Is that right?
FirstSortedValue([Operation/Activity], -[Operation/Activity]) AS [Operation/Activity],
Awesome Sebastian! Thank you. That helps a lot!
Not at all. If the Order No and Line No each identify a single line, you load them first as shown (without the Amount). Afterwards you just JOIN the other columns to those so-called "Primary Key" values. Like in:
Highest_Entry:
LOAD Order,
FirstSortedValue([Operation/Activity], -[Operation/Activity]) AS [Operation/Activity]
FROM QV_Load.xlsx (ooxml, embedded labels, table is Sheet1)
GROUP BY Order;
LEFT JOIN(Highest_Entry)
LOAD *
FROM QV_Load.xlsx (ooxml, embedded labels, table is Sheet1);
Very simple. But I see that you already found a solution.
Good luck,
Peter