How to select, in QlikView, all values from one variable which ARE NOT in other variable?

[mlarruda]

Friends,

Suppose that I have two files and load them in #QlikView as follows

LOAD NAMES1
FROM file1.csv

LOAD NAMES2
FROM file2.csv

So, how can I select, in a table or list, all names from NAMES1 which doesn't appear on NAMES2?

For example, if NAMES1 = ('AAA', 'BBB', 'CCC', 'DDD') and NAMES2 = ('AAA', 'CCC', 'EEE', 'FFF'), then I want this function returns ('BBB', 'DDD').

Thanks in advance.

[jwjackso]

Using:

LOAD * Inline [
NAMES1
AAA
BBB
CCC
DDD
];

Names2:
LOAD * Inline [
NAMES2
AAA
CCC
EEE
FFF
];

Both of the expressions below worked in a List Box:

=Aggr(Only({<NAMES1-=P(NAMES2)>}NAMES1),NAMES1)

=Aggr(If(count({<NAMES1-=P(NAMES2)>}NAMES1) > 0,NAMES1,NULL()),NAMES1)

[marcus_sommer]

It  might be more suitable if the essential work is done within the script, maybe with:

...
load NAMES2, exists(NAMES1, NAMES2) as Flag from file2.csv;

and you may then use the flag directly as selection or as condition within the calculations.

- Marcus

[mlarruda]

Thank you. Tested the first function and it worked.

[mlarruda]

Thank you, but didn't worked, probably because my real files have the same columns.

I tried something like this, but didn't worked:

Tab1:
load CLIENTS, SELLS from file2020.csv
Concatenate
load CLIENTS as CLTS21, SELLS from file2021.csv

load
exists(CLIENTS, CLTS21) as Flag
resident Tab1

How can I repair this?

[marcus_sommer]

Try it in this way:

t1:
LOAD *, 0 as Flag, 1 as Source Inline [
NAMES
AAA
BBB
CCC
DDD
];

concatenate
LOAD *, exists(NAMES, NAMES) as Flag, 2 as Source Inline [
NAMES
AAA
CCC
EEE
FFF
];

- Marcus