topic Re: nested query question in QlikView

nested query question

Thu, 27 Feb 2014 12:15:06 GMT

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

Re: nested query question

jagannalla — Thu, 27 Feb 2014 12:26:08 GMT

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?

Re: nested query question

Thu, 27 Feb 2014 12:29:33 GMT

Yes, I am loading this data from single table.

Let me try this .

Re: nested query question

Thu, 27 Feb 2014 12:33:34 GMT

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

Re: nested query question

jagannalla — Thu, 27 Feb 2014 12:48:55 GMT

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

Re: nested query question

maxgro — Thu, 27 Feb 2014 12:51:14 GMT

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;

Re: nested query question

maxgro — Thu, 27 Feb 2014 21:14:20 GMT

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;