Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
felipeflamenco
Contributor III
Contributor III

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

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!

1 Solution

Accepted Solutions
sebastianlettner
Partner - Creator
Partner - Creator

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

View solution in original post

5 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Partner - Creator
Partner - Creator

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

felipeflamenco
Contributor III
Contributor III
Author

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

felipeflamenco
Contributor III
Contributor III
Author

Awesome Sebastian! Thank you. That helps a lot!

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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