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

Where-Clause for another table to exclude data sets

GeorgeQV — Tue, 01 Mar 2016 09:03:53 GMT

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.

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

sunny_talwar — Tue, 01 Mar 2016 11:23:58 GMT

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?

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

MayilVahanan — Tue, 01 Mar 2016 11:34:31 GMT

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;

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

Gysbert_Wassenaar — Wed, 09 Mar 2016 17:12:30 GMT

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;

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

maxgro — Thu, 10 Mar 2016 00:02:38 GMT

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;

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

Thu, 10 Mar 2016 16:16:56 GMT

just a tipp:

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

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

GeorgeQV — Tue, 15 Mar 2016 09:56:28 GMT

Thank you very much Mayil, that did the job!

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

pam1990 — Mon, 12 Apr 2021 21:21:52 GMT

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