Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
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;
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;
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;
just a tipp:
If you are using variables, not the fields, later on ... the exclusion can also be done with -=
Thank you very much Mayil, that did the job!
This was helpful. I was able to apply it to something I wanted to accomplish.