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: 
Not applicable

MINUS

Hi, I need to load, in ETL phase, a table that is the result of a MINUS (logically meaning) between 2 tables.

Which is the right command for this statement?

Thanks in advance

Steven

5 Replies
martynlloyd
Partner - Creator III
Partner - Creator III

You mean records that exist in one or other, but not both?

Regards,

M.

maxgro
MVP
MVP

I think you have to join the tables and then filter records (bold)

t1:

load * inline [

f1, f2, f3

a,1,100

a,1,101

b,2,200

c,3,300

z,20,2000

];

t2:

NoConcatenate load * inline [

f1, f2, f3

a,1,101

b,2,200

c,3,301

d,4,400

];

tmp:

NoConcatenate load *, 1 as t1 Resident t1;

join (tmp) load *, 1 as t2 Resident t2;

DROP Table t1, t2;

final:

NoConcatenate load * Resident tmp where t1=1 and IsNull(t2);

DROP Table tmp;

Not applicable
Author

Yes,

I need to get records that are only in one table and not in the other one.

Regards

Steven

anbu1984
Master III
Master III

This script performs TAB2 - TAB1

TAB1:

load * inline [

country, sales

Brazil, 234

US, 432

Germany, 121

India, 444];

NoConcatenate

TAB2:

load *inline [

country1, sales1

US, 412

Germany, 488

India, 224

China,101];

NoConcatenate

Final:

Load * Resident TAB2 Where Not Exists(country,country1);

Drop Tables TAB1,TAB2;

anbu1984
Master III
Master III

TAB1:

load * inline [

country, sales

Brazil, 234

US, 432

Germany, 121

India, 444];

NoConcatenate

TAB2:

Load * Where Not Exists(country);

load * inline [

country, sales

US, 412

Germany, 488

India, 224

China,101];

Drop Table TAB1;