Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Pre-selection

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
4 Replies

Re: Pre-selection

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

Re: Pre-selection

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

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

Re: Pre-selection

It works!

Great!

Thanks a lot!

Community Browser