Using Not Exists

[Localbar]

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

[Onerealy]

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

[Localbar]

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

[sidhiq91]

@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.

[Onerealy]

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

[Localbar]

Thanks Onerealy and Sidhiq91. This is work.