topic Re: Pre-selection in QlikView

Pre-selection

Wed, 24 Apr 2013 13:45:21 GMT

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!

Re: Pre-selection

Gysbert_Wassenaar — Wed, 24 Apr 2013 13:55:48 GMT

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)

Re: Pre-selection

Wed, 24 Apr 2013 14:46:24 GMT

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

Re: Pre-selection

Gysbert_Wassenaar — Thu, 25 Apr 2013 05:54:06 GMT

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...;

Re: Pre-selection

Fri, 26 Apr 2013 16:11:11 GMT

It works!

Great!

Thanks a lot!