topic Re: Loading certain records in QlikView

Loading certain records

Wed, 08 May 2013 15:24:01 GMT

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!

Re: Loading certain records

Gysbert_Wassenaar — Wed, 08 May 2013 15:29:17 GMT

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;

Re: Loading certain records

anandathome — Wed, 08 May 2013 15:54:36 GMT

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;

Re: Loading certain records

anandathome — Wed, 08 May 2013 15:55:47 GMT

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

Re: Loading certain records

Thu, 09 May 2013 09:45:58 GMT

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!

Re: Loading certain records

Thu, 09 May 2013 09:51:12 GMT

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!