topic Using Not Exists in QlikView

Using Not Exists

Localbar — Wed, 17 Aug 2022 04:30:11 GMT

Dear all,

I have two qvd files which name are "tab_check" and "tab_coupon". I am trying to extract records which high-light from qvd file(tab_check). The key value are (date+shop+check) which not exists in "tab_coupon".

I am no idea how to write Load statement. Thanks.

tab_check	date	shop	check	total
	01-08-2022	1001	12345	400
	01-08-2022	2001	24356	123
	02-08-2022	2001	24366	792
	03-08-2022	1001	12355	333
	04-08-2022	3001	34331	456

tab_coupon	red_date	red_shop	red_check	coupon
	01-08-2022	2001	24356	cpn_621304
	03-08-2022	1001	12355	cpn_621369
	04-08-2022	3001	34331	cpn_621444

Re: Using Not Exists

Onerealy — Wed, 17 Aug 2022 07:19:16 GMT

Hello!

Just make a key field like date&shop&check.

Data2:
load tab_coupon,
red_date,
red_shop,
red_check,
coupon,
red_date&red_shop&red_check as key_field
inline [
tab_coupon, red_date, red_shop, red_check, coupon
, 01-08-2022, 2001, 24356, cpn_621304
, 03-08-2022, 1001, 12355, cpn_621369
, 04-08-2022, 3001, 34331, cpn_621444
];

Data1:
load date,
shop,
check,
total,
tab_check
inline [
tab_check, date, shop, check, total
, 01-08-2022, 1001, 12345, 400
, 01-08-2022, 2001, 24356, 123
, 02-08-2022, 2001, 24366, 792
, 03-08-2022, 1001, 12355, 333
, 04-08-2022, 3001, 34331, 456
]
where not Exists(key_field, date&shop&check);

Re: Using Not Exists

Localbar — Thu, 18 Aug 2022 01:40:37 GMT

Thanks a lot. But my source is come from 2 qvd files not use inline.

Re: Using Not Exists

sidhiq91 — Thu, 18 Aug 2022 03:42:29 GMT

@Onerealy Please see the code below which is same as what @Localbar has mentioned.

NoConcatenate
Temp:
Load red_date&'-'&red_shop&'-'&red_check as ID,
Date(red_date,'MM/DD/YYYY') as red_date,
red_shop,
red_check,
coupon

Inline [
red_date, red_shop, red_check, coupon
1/8/2022, 2001, 24356, cpn_621304
3/8/2022, 1001, 12355, cpn_621369
4/8/2022, 3001, 34331, cpn_621444
];

NoConcatenate
Temp1:
Load date&'-'&shop&'-'&check,
Date(date,'MM/DD/YYYY') as date,
shop,
check,
total
inline [
date, shop, check, total
1/8/2022, 1001, 12345, 400
1/8/2022, 2001, 24356, 123
2/8/2022, 2001, 24366, 792
3/8/2022, 1001, 12355, 333
4/8/2022, 3001, 34331, 456
]
where not Exists(ID,date&'-'&shop&'-'&check);

Drop table Temp;

Exit Script;

However, you can use the same logic for QVD files as well, it will work.

Re: Using Not Exists

Onerealy — Thu, 18 Aug 2022 05:16:47 GMT

This is the same as I wrote above, just from a qvd file.

Data2:

load tab_coupon,

red_date,

red_shop,

red_check,

coupon,

red_date&red_shop&red_check as key_field

from tab_coupon.qvd (qvd);

Data1:

load date,

shop,

check,

total,

tab_check

from tab_check.qvd(qvd)

where not Exists(key_field, date&shop&check);

Re: Using Not Exists

Localbar — Fri, 19 Aug 2022 04:50:51 GMT

Thanks Onerealy and Sidhiq91. This is work.