How to make subquery in QV

[#1]

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

[Gysbert_Wassenaar]

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.

[MarcoWedel]

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

[#4]

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

[MarcoWedel]

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

regards

Marco

[#6]

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

[MarcoWedel]

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

[#8]

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,

[#9]

Hi,

Kindly request you to update on my code.

Regards

[MarcoWedel]

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