topic Re: How to Exclude values in a script in QlikView

How to Exclude values in a script

Mon, 31 Mar 2014 16:59:17 GMT

I wrote a script that create the table below

Location	Date
Bateen	26/2/2013
Bateen	27/2/2013
Khazna	27/2/2013
khaleej	28/2/2013

Now i created another script that show another table which also have location and date along with i other answer

What i want to do in this table is to have only the raws which match the able above

so if a raw contain Bateen and 28/2/2013 which are not in the above table then this automatically ignored and removed

Re: How to Exclude values in a script

its_anandrjs — Mon, 31 Mar 2014 17:05:00 GMT

Write like

Tab1:

Load Location&'|'&Date as Key

Load * inline

[

Location,Date

Khazna,27/2/2013

khaleej,28/2/2013

Bateen,26/2/2013

Batten,27/2/2013

];

left join

Tab2:

Load

Location&'|'&Date as Key,

Location, Date

from Location;

In this only upper table records is added

Hope this helps

Re: How to Exclude values in a script

Gysbert_Wassenaar — Mon, 31 Mar 2014 17:06:37 GMT

You can use a left keep:

Table1:

Load * From Source1;

left keep (Table1)

Table2:

Load * From Source2;

Re: How to Exclude values in a script

maxgro — Mon, 31 Mar 2014 18:35:43 GMT

another one

First:

LOAD Location,

Date,

Location & '-' & Date as LocAndDate

FROM

[http://community.qlik.com/thread/112589]

(html, codepage is 1252, embedded labels, table is @1);

Second:

load *

Where exists(LocAndDate, Location2 & '-' & Date2);

load * inline [

Location2, Date2

Bateen, 26/2/2013

Bateen, 27/2/2013

Khazna, 27/2/2013

khaleej,28/2/2013

Bateen, 26/2/2014

Bateen, 27/2/2014

Khazna, 27/2/2014

khaleej,28/2/2014

Paris,26/2/2013

]

;

DROP Field LocAndDate;

Re: How to Exclude values in a script

Mon, 31 Mar 2014 19:45:41 GMT

Create a key using Laocation and Date. I would suggest converting the Date to number using num() in the key. Thi is to make sure that the dates are matched properly, though it may have different formats in the second table.

Use "Where exists" to filter out the unwanted records.

Tab1:

Load Location&'|'& num(Date) as Key

Load * inline

[

Location,Date

Khazna,27/2/2013

khaleej,28/2/2013

Bateen,26/2/2013

Batten,27/2/2013

];

Tab2:

load *

FROM [....] //Actual path here

Where exists(Key, Location & '|' &num( Date));

Drop table Tab1:

Re: How to Exclude values in a script

Sat, 05 Apr 2014 11:17:11 GMT

This is helpful but i think actuall tab2 should be referred first as its the tab that should be reduced