topic Re: sql sub-query in Qlik - urgent in QlikView

sql sub-query in Qlik - urgent

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

Please help. It's urgent.

Thank You,

Rahul

Re: sql sub-query in Qlik - urgent

trdandamudi — Thu, 04 Aug 2016 15:21:32 GMT

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);

Re: sql sub-query in Qlik - urgent

vishsaggi — Thu, 04 Aug 2016 15:50:11 GMT

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;

Re: sql sub-query in Qlik - urgent

Fri, 05 Aug 2016 07:07:49 GMT

Thanks Mr. Nagaraju.

But, this is not working.

Please loo into it and suggest appropriate solution ASAP.

Thanks,

Re: sql sub-query in Qlik - urgent

Fri, 05 Aug 2016 07:09:49 GMT

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,

Re: sql sub-query in Qlik - urgent

jpenuliar — Fri, 05 Aug 2016 08:30:35 GMT

Hi Rahul,

Qlikview can load data using SQL, please read

SQL ‒ QlikView

Re: sql sub-query in Qlik - urgent

sorrakis01 — Fri, 05 Aug 2016 08:48:39 GMT

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

Re: sql sub-query in Qlik - urgent

boopeshj — Fri, 05 Aug 2016 08:50:52 GMT

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.

Re: sql sub-query in Qlik - urgent

Fri, 05 Aug 2016 08:58:58 GMT

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,

Re: sql sub-query in Qlik - urgent

sorrakis01 — Fri, 05 Aug 2016 09:07:49 GMT

Great!

Regards,

Re: sql sub-query in Qlik - urgent

rupalimane1390 — Thu, 17 Feb 2022 11:02:20 GMT

What do we do in case where in query is coming from different schema other than main select statement?

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

statement but it gives error ' Table or view does not exist'.