nested query question

[#1]

Hi,

I got a SQL which looks like this...

Select T1.id, T1.date

from Table T1

where

T1.seq_num = (select max(T2.seq_num) from Table T2 where T1.item_id = T2.item_id )

How to do this in Load script?

Regards,

Saurabh

[jagannalla]

Hi,

T2:

Load Max(Seq_num) as MaxNum from Table1;

Let vMaxNum=Peek('MaxNum ',-1,'T2');

Load Id,date

from Table1

where Seq_num = $(vMaxNum);

- Are you loading data from single table?

[#3]

Yes, I am loading this data from single table.

Let me try this .

[#4]

This

Load Max(Seq_num) as MaxNum from Table1;

is not taking care of

select max(T2.seq_num) from Table T2 where T1.item_id = T2.item_id

[jagannalla]

Hi,

Can you please add sample data what you had in your DB along with qlikview file.

And explain your result set what you are expecting on sample data.

Thanks,

Jagan

[maxgro]

T2:

load item_id, seq_num;

select item_id, max(T2.seq_num) seq_num, from Table T2 group by item_id;

inner join (T2)

load item_id, seq_num, id, date;

select * from Table T1;

[maxgro]

T1;

select  id, date, seq_num, item_id  fom Table;

Table:

noconcatenate load

item_id,

FirstSortedValue(id, -seq_num),

FirstSortedValue(date, -seq_num)

//max(seq_num)

resident T1

group by item_id;

drop table T1;