Sub-query in Qlik sense

[#1]

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

[arulsettu]

try like this

load LocationId,

    MainVesselId,

    max(SlNo)

from tbl_Data_VesselActivity

group by LocationId,MainVesselId;

[saimahasan]

Hi

Please try this:

A:

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

];

B:

LOAD  max(SlNo) as MaxSlNo

Resident A;

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

C:

LOAD *,

  '' as jnk

Resident A

Where SlNo = $(a);

DROP Tables A,B;

[#4]

Thanks for the suggestion.

But, loop is required to consider all values.

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

Please help.

[saimahasan]

You can try this code:

A:

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

];

B:

LOAD  MainVesselId as A1,

   max(SlNo) as MaxSlNo

Resident A

Group By MainVesselId;

C:

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??