sql sub-query in Qlik - urgent

[#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 (selectmax(SlNo) FROM [dbo].[tbl_Data_VesselActivity] group by MainVesselId).

Please help. It's urgent.

Thank You,

Rahul

[trdandamudi]

May be as below:

Table1:

Load Max(SlNo) as SlNo

FROM [dbo].[tbl_Data_VesselActivity]

group by MainVesselId;

NoConcatenate

Table2:

Load

LocationId,

MainVesselId

FROM [dbo].[tbl_Data_VesselActivity]

Where Exists (SlNo);

[vishsaggi]

Try this? Change accordingly.

Table1:

LOAD*;

SQL SELECT 

  LocationId

,MainVesselId,

slno

FROM [dbo].[tbl_Data_VesselActivity]

;

LEFT Join(Table1)

LOAD Max(Slno) AS Slno

Resident Table1

Group by MainVesselID;

[#4]

Thanks Mr. Nagaraju.

But, this is not working.

Please loo into it and suggest appropriate solution ASAP.

Thanks,

[#5]

Thanks so much.

But, this is not working since it says for the second script that the SlNo does not exists.

Please suggest an appropriate solution ASAP.

Thanks,

[jpenuliar]

Hi Rahul,

Qlikview can load data using SQL, please read

SQL ‒ QlikView

[sorrakis01 · Accepted solution]

Hi,

Firtsly you need to connect to SQL Database and will create the connection:

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=SA;Initial Catalog=BI_Qlikview;Data Source=1.x.x.xx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=USER-PC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is LOXXXXXXXXXQJOFDaeWaNOFbMQTZYN);

After this you can load the tables:

load *; (optional)

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

Regards

[boopeshj]

Try this

Table1:

Load Max(SlNo) as SlNo_Max

FROM [dbo].[tbl_Data_VesselActivity]

group by MainVesselId;

NoConcatenate

Table2:

Load

SlNo,

LocationId,

MainVesselId

FROM [dbo].[tbl_Data_VesselActivity]

Where Exists (SlNo_Max,SlNo);

- Boo

Please mark correct if you find them.

[#9]

Hi Jordan,

Thanks so much for the solution.

The same solution I have found 15 minutes back.

How ever thanks so much for taking pain and giving me the solution.

I have highly obliged.

Thanks once again,

[sorrakis01]

Great!

Regards,