topic Re: Loading specific rows from Excel-file (always last value) in QlikView

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

Anonymous — Thu, 10 Dec 2015 15:59:15 GMT

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!

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

Peter_Cammaert — Thu, 10 Dec 2015 16:34:42 GMT

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

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

sebastianlettner — Thu, 10 Dec 2015 16:39:14 GMT

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

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

Anonymous — Thu, 10 Dec 2015 16:53:26 GMT

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

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

Anonymous — Thu, 10 Dec 2015 17:24:17 GMT

Awesome Sebastian! Thank you. That helps a lot!

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

Peter_Cammaert — Thu, 10 Dec 2015 17:42:45 GMT

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