Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mlarruda
Creator
Creator

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

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.

Labels (3)
5 Replies
jwjackso
Specialist III
Specialist III

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
Creator
Creator
Author

Thank you. Tested the first function and it worked.

mlarruda
Creator
Creator
Author

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