Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
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 (4)
5 Replies
jwjackso
Specialist II
Specialist II

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
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

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