Loading specific rows from Excel-file (always last value)

[Anonymous]

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!

[Peter_Cammaert]

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

[sebastianlettner · Accepted solution]

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

[Anonymous]

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],

[Anonymous]

Awesome Sebastian! Thank you. That helps a lot!

[Peter_Cammaert]

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