Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gottsmann
Not applicable

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

Re: Where-Clause for another table to exclude data sets

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;

6 Replies
sunny_talwar
Not applicable

Re: Where-Clause for another table to exclude data sets

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

Re: Where-Clause for another table to exclude data sets

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

Re: Where-Clause for another table to exclude data sets

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

Re: Where-Clause for another table to exclude data sets

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

Re: Where-Clause for another table to exclude data sets

just a tipp:

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

gottsmann
Not applicable

Re: Where-Clause for another table to exclude data sets

Thank you very much Mayil, that did the job!