topic Re: Select from another table if first resultset is empty in QlikView

Select from another table if first resultset is empty

Wed, 25 Nov 2020 16:16:04 GMT

I have an issue where I am doing a left join on two tables, but if the second table doesn't contain a match, I would like to look in another table. Example:

LOAD "sod_acct",
"sod_nbr" as "so_nbr",
"sod_domain" as "Domain",
"sod_cc",
"sod_confirm",
"sod_consignment",
"sod_covered_amt",
"sod_um_conv";
SQL SELECT *
FROM DELMFG.PUB."sod_det";
LEFT JOIN
LOAD "ad_city",
UPPER("ad_addr") as "ShipNo",
"ad_domain" as "Domain",
"ad_name" as "ship_to_name",
"ad_state" as "ship_to_state",
"ad_type",
"ad_zip" as "ship_to_zip";
SQL SELECT *
FROM DELMFG.PUB."ad_mstr" where ad_type = 'ship-to';

Basically, if there is not a match in ad_mstr, I would like to look into another table and see if I can find a match in that table.

Anybody have a suggestion?

Re: Select from another table if first resultset is empty

Anonymous — Wed, 19 Oct 2016 21:17:16 GMT

First, you can use Inner Join between first and second table. After that, check the number of records. If there are >0 records, that means there are matches, use your script. If there are no records in the inner join, then join another table.

A simple app is attached.

Re: Select from another table if first resultset is empty

Anil_Babu_Samineni — Thu, 20 Oct 2016 02:26:43 GMT

I didn't find any field name called ship_to. Where you do you taken that field name from table 1. Can you please describe more