MINUS

[#1]

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

[martynlloyd]

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

Regards,

M.

[maxgro]

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;

[#4]

Yes,

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

Regards

Steven

[anbu1984]

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]

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;