Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pre-selection

Hallo,

I have some problems selecting items from my db and importing them in qv.

Pretty simple question I reckon, but not for me, unfortunately 😞

Give you a rough example:

Timestamp     Fruit          Kilograms

20110101     Bananas     3

20120302     Bananas     5

20130404     Bananas     2

20110505     Apples        4

20120606     Apples        3

What I need is to preselect rows in order to have only e.g. the most recent items for every fruit.

So after preselection my data in qlikview would be:

Timestamp     Fruit          Kilograms

20130404     Bananas     2

20120606     Apples        3

How can I do?

Thanks a lot in advance!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

This should give you a table with only the records with the max timestamp per fruit:

Load Fruit, max(Timestamp) as Timestamp

from ...sourcetable...

group by Fruit;

left join

Load * from ...sourcetable...;


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

You can use the firstsortedvalue function. Create a straight table object, add Fruit as dimension and two expressions:

Timestamp: max(Timestamp)

Kilograms: firstsortedvalue(Kilograms, -Timestamp)


talk is cheap, supply exceeds demand
Not applicable
Author

Dear Gysbert,

What you suggests works fine but it's not what I'm looking for.

My example with fruit and kilograms is, as you can imagine, extremely semplified compared to the real db.

I have in my original table some 40/50 columns so in the straight table object I get only one result for each fruit only if I select columns with equal values, but if I put all the columns I get many values for each fruit.

Additionally I would like to avoid having a firstsortedvalue column.

Isn't there a way to achieve what I need working on the (main) script, so that I can drop and completely forget about all the data I don't need?

thanks a lot,

Lorenzo

Gysbert_Wassenaar

This should give you a table with only the records with the max timestamp per fruit:

Load Fruit, max(Timestamp) as Timestamp

from ...sourcetable...

group by Fruit;

left join

Load * from ...sourcetable...;


talk is cheap, supply exceeds demand
Not applicable
Author

It works!

Great!

Thanks a lot!