Where-Clause for another table to exclude data sets

[GeorgeQV]

Dear all,

Let's say I have two tables:

Table1:
ID, ValueA
1, 200
3, 300
4, 400

Table2:
ID, ValueB
1, 999
2, 888
3, 777

How can I load them, but exclude the records of Table2 where ValueB < ValueA in Table1? The ID is the same key for both tables. I tried JOIN, KEEP, RESIDENT and more, but nothing worked.

Thank you in advance.

[sunny_talwar]

May be IntervalMatch is a good candidate here. But in your above sample, it would seem that all the rows from Table2 will be pulled in since we have ValueB > ValueA in all the three cases above. Do you have a more representative sample?

[MayilVahanan · Accepted solution]

Hi

Are you looking like this

Temp:

Load Id, ValueA from TableA;

Left Join (Temp) Load Id, ValueB from TableB;

Load * Where Flag = 1;

Load *, If(ValueA > ValueB, 1, 0) AS Flag resident Temp;

Drop table Temp;

[Gysbert_Wassenaar]

Table1:

LOAD ID, ValueA FROM SourceA;

Temp:

Noconcatenate LOAD * Resident Table1;

Left Join (Temp)

LOAD ID, ValueB FROM SourceB;

Table2:

LOAD ID, ValueB Resident Temp WHERE ValueB < ValueB;

Drop Table Temp;

[maxgro]

Table1:

load * inline [ 

ID, ValueA 

1, 200 

3, 300 

4, 400 

];

left join (Table1) load * inline [ 

ID, ValueB 

1, 999 

2, 888 

3, 777 

];

Table2:

load ID, ValueB

Resident Table1

where not (ValueB < ValueA);

DROP Field ValueB From Table1;

[#6]

just a tipp:

If you are using variables, not the fields, later on ... the exclusion can also be done with -=

[GeorgeQV]

Thank you very much Mayil, that did the job!

[pam1990]

This was helpful. I was able to apply it to something I wanted to accomplish.