topic Re: How to make subquery in QV in QlikView

How to make subquery in QV

Fri, 06 May 2016 12:34:17 GMT

Hi,

I want Subquery code in qlikview is it possible to make in qlikview.

Below is the query :

select * from shipment where transport_mode='RAIL'

and Source_location_id not in (select location_id from region where region_id = 'RH_Mumbai')

Kindly help me in this

Regards,

Surekha

Re: How to make subquery in QV

Gysbert_Wassenaar — Fri, 06 May 2016 15:58:30 GMT

Use the sql statement as is. Qlikview will pass it to the database server for execution and load the results it gets back into a table.

The other way is to use two load statements with a join

TableA:

select * from shipment where transport_mode='RAIL';

right join (TableA)

select location_id from region where region_id = 'RH_Mumbai';

The disadvantage of it that firest all the records from shipment will be retrieved and stored in TableA. Only with the join will the records you don't want be removed.

Re: How to make subquery in QV

MarcoWedel — Fri, 06 May 2016 21:08:05 GMT

Hi,

one solution that considers the NOT in part of your where clause might be:

region:

LOAD Distinct location_id

INLINE [

location_id, region_id

Location10, Region1

Location11, Region1

Location12, Region1

Location13, Region1

Location20, Region2

Location21, Region2

Location22, Region2

Location30, Region3

Location31, Region3

Location32, Region3

Location33, Region3

Location34, Region3

Location35, Region3

]

Where region_id = 'Region2';

shipment:

LOAD * INLINE [

Source_location_id, transport_mode

Location10, RAIL

Location11, ROAD

Location12, WATER

Location13, AIR

Location20, AIR

Location21, RAIL

Location22, ROAD

Location30, ROAD

Location31, RAIL

Location32, WATER

Location33, ROAD

Location34, RAIL

Location35, AIR

]

Where transport_mode='RAIL' and not Exists (location_id, Source_location_id);

DROP Table region;

(inline tables to be replaced with your select statements and a preceding load for the shipment table to use the Exists() function))

But like Gysbert alread said, try first to feed the complete Select statement to your database using the QlikView SQL statement.

hope this helps

regards

Marco

Re: How to make subquery in QV

Mon, 09 May 2016 07:00:50 GMT

Hi,

Please find the below code :

INLINE_LOADEDROADFREIGHT:

LOAD * Inline

[ SOURCE_LOCATION_GID

RH_MUMBAI

];

left join (SHIPMENT)

LOAD distinct SHIPMENT_GID,

SOURCE_LOCATION_GID

FROM

\\10.50.53.84\e$\Live\QvApp\Qvds\Shipment.qvd (qvd)

where not Exists (SOURCE_LOCATION_GID, 'RH_MUMBAI');

drop Table INLINE_LOADEDROADFREIGHT;

left join (SHIPMENT)

LOAD REGION_GID,

LOCATION_GID as [SOURCE_LOCATION_GID]

FROM

\\10.50.53.84\e$\Live\QvApp\Qvds\REGION_DETAIL.qvd (qvd)

WHERE REGION_GID = 'RH_MUMBAI';

Kindly suggest above code is correct for subquery.

Regards

Re: How to make subquery in QV

MarcoWedel — Mon, 09 May 2016 20:58:34 GMT

please post some sample data (qvds) and your expected result.

regards

Marco

Re: How to make subquery in QV

Tue, 10 May 2016 06:25:40 GMT

Hi,

I could not have able to attached the data ;as i dont have attachment button in my login.

I also raise a issue about it in community but could not find any solution till now.

Please review my code and let me know is it right or wrong.

Regards

Re: How to make subquery in QV

MarcoWedel — Tue, 10 May 2016 20:52:21 GMT

Hi,

open the thread (don't reply within your inbox) and select "use advanced editor".

This will enable an option to attach files to your post:

hope this helps

regards

Marco

Re: How to make subquery in QV

Thu, 12 May 2016 06:45:56 GMT

Hi Macro,

Thanks for giving the solution of attachments of file.

Please find the attached code and let me know why its giving me error.

Kindly request you to review my below code for Subquery and suggest.

Regards,

Re: How to make subquery in QV

Tue, 17 May 2016 05:56:16 GMT

Hi,

Kindly request you to update on my code.

Regards

Re: How to make subquery in QV

MarcoWedel — Tue, 17 May 2016 19:39:27 GMT

please post your expected result and some small samples of your qvd files to test with (maybe constructed samples if original data is confidential).

thanks

regards

Marco

Re: How to make subquery in QV

Wed, 25 May 2016 11:47:20 GMT

Hi Macro,

As requested , Please find the small samples of qvd files for test result.

Request you to provide code for it.

Regards

Re: How to make subquery in QV

MarcoWedel — Wed, 25 May 2016 21:08:31 GMT

Hi,

one possible solution using your sample data could be:

region:
LOAD Distinct LOCATION_ID
FROM [https://community.qlik.com/servlet/JiveServlet/download/1047306-227901/TESTREGION.xls] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
Where REGION_ID = 'RH_MUMBAI';

shipment:
LOAD *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1047306-227900/TESTSUB.xls] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
Where TRANSPORT_MODE='RAIL' and not Exists (LOCATION_ID, SOURCE_LOCATION_ID); 

DROP Table region;

hope this helps

regards

Marco