Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Loading certain records

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!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Loading certain records

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;


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: Loading certain records

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;


talk is cheap, supply exceeds demand
anandathome
Contributor

Re: Loading certain records

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
Contributor

Re: Loading certain records

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

Not applicable

Re: Loading certain records

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!

Not applicable

Re: Loading certain records

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!