Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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...;
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)
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
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...;
It works!
Great!
Thanks a lot!