Loading certain records

[#1]

Hi Community

I'm hoping someone can help me, my QlikView skills are non-existant.  I am tring to load specific records from a table into Qlikview - using the Table below as an example, I want to load only the green highlighted records:

Table:

ID	Sequence	Value
1	1	whatever
1	2	whatever
1	3	whatever
2	1	whatever
2	2	whatever
3	1	whatever
3	2	whatever
3	3	whatever
3	4	whatever

Essentially I need to pull in the Value for each ID in the table with the highest Sequence nunber.  Does anyone have any suggestions please.

Cheers!

[Gysbert_Wassenaar · Accepted solution]

If you're loading from an sql database you can create an sql statement that returns only the records you want. If you have to load from a file then you have to load all records first and then get rid of those you don't need. That would look something like this:

T1:

Load ID, Sequence, Value

from ...somefile...;

right join load ID, max(Sequence) as Sequence

resident T1 group by ID;

[anandathome]

TEMP:

LOAD * INLINE [

ID,Sequence,Value

1,          1,          3

1,          2,          110

1,          3,          0

2,          1,          120

2,          2,          1          

3,          1,          50

3,          2,          60 

3,          3,          70 

3,          4,          200 

3,          5,          400   

];

Final:

Load ID as ID,

     max(Sequence) as Sequence    

Resident TEMP

group by ID;

Inner Join

Load * resident TEMP;

Drop table TEMP;

[anandathome]

This should solve your problem. Please check and let me know

[#5]

Hi Gysbert

Thanks for your really quick reply.  I am loading from an SQL dB, I should have said.

I don't pretend to know how that has worked, but it has - thanks a lot.

Cheers!

[#6]

Hi Anand

Thanks for replying - I don't doubt your solution would have worked but I tried the first response and that has worked for me.

Cheers!