topic Re: Sub-query in Qlik sense in QlikView

Sub-query in Qlik sense

Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

Someone please help me. How can I create a load statement for the following SQL query.

select LocationId,MainVesselId FROM [dbo].[tbl_Data_VesselActivity] where SlNo in (select max(SlNo) FROM [dbo].[tbl_Data_VesselActivity] group by MainVesselId).

Please help. It's urgent.

Thank You,

Rahul

Re: Sub-query in Qlik sense

arulsettu — Tue, 02 Aug 2016 06:59:10 GMT

try like this

load LocationId,

MainVesselId,

max(SlNo)

from tbl_Data_VesselActivity

group by LocationId,MainVesselId;

Re: Sub-query in Qlik sense

saimahasan — Tue, 02 Aug 2016 12:49:30 GMT

Please try this:

load * Inline [

SlNo,LocationId,MainVesselId

10,a,58

12,b,34

12,c,45

24,a,56

24,b,81

12,c,78

21,a,45

];

LOAD max(SlNo) as MaxSlNo

Resident A;

LET a = Peek('MaxSlNo',0,'B');

LOAD *,

'' as jnk

Resident A

Where SlNo = $(a);

DROP Tables A,B;

Re: Sub-query in Qlik sense

Thu, 04 Aug 2016 14:15:33 GMT

Thanks for the suggestion.

But, loop is required to consider all values.

This is taking on one value, i,e. max. value

Please help.

Re: Sub-query in Qlik sense

saimahasan — Mon, 08 Aug 2016 12:36:35 GMT

You can try this code:

load * Inline [

SlNo,LocationId,MainVesselId

10,a,58

12,b,58

12,c,58

24,a,56

24,b,56

12,c,56

21,a,47

];

LOAD MainVesselId as A1,

max(SlNo) as MaxSlNo

Resident A

Group By MainVesselId;

LOAD * ,

'' as jnk

Resident A

Where Exists(MaxSlNo,SlNo);

DROP Table A;

It will give the output as

LocationId	MainVesselId	SlNo
a	47	21
a	56	24
b	56	24
b	58	12
c	56	12
c	58	12

Is this your requirement??