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: 
GeorgeQV
Contributor III
Contributor III

Where-Clause for another table to exclude data sets

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.

1 Solution

Accepted Solutions
MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

7 Replies
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

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
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;


talk is cheap, supply exceeds demand
maxgro
MVP
MVP

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;

Not applicable

just a tipp:

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

GeorgeQV
Contributor III
Contributor III
Author

Thank you very much Mayil, that did the job!

pam1990
Contributor III
Contributor III

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