Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

sql sub-query in Qlik - urgent

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

1 Solution

Accepted Solutions
sorrakis01
Specialist
Specialist

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

View solution in original post

10 Replies
trdandamudi
Master II
Master II

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
Champion III
Champion III

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;

Not applicable
Author

Thanks Mr. Nagaraju.

But, this is not working.

Please loo into it and suggest appropriate solution ASAP.

Thanks,

Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

Hi Rahul,

Qlikview can load data using SQL, please read

SQL ‒ QlikView

sorrakis01
Specialist
Specialist

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
Partner - Creator II
Partner - Creator II

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.

Not applicable
Author

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

Great!

Regards,