topic Re: Exclude data based on data in QlikView

Exclude data based on data

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

HI expert,

I have data in a table and I have to exclude the data from table 1 based on data that us provided in an excel.

example:

table1:

POLICY_NO	CUSTOMER
123	A
4567	B
789	C

‌

excel was table consider to exclude

POLICY_NO	CUSTOMER	REMARKS
789	C	NOT INTRESTED
731	D	CLOSED
123	A	SUSPENDED

Re: Exclude data based on data

viveksingh — Fri, 12 Oct 2018 06:38:30 GMT

Expected result:

‌

POLICY_NO	CUSTOMER
4567	B

Re: Exclude data based on data

lironbaram — Fri, 12 Oct 2018 06:46:59 GMT

you can wrote script like this

excludeTable:

load POLICY_NO & '_' & CUSTOMER as KEY

from ExcelTable;

table1:

Load PLICY_NO, CUSTOMER

From table1

where not exists(KEY, POLICY_NO & '_' & CUSTOMER);

drop table excludeTable;

Re: Exclude data based on data

shreya_nadkarni — Fri, 12 Oct 2018 06:53:14 GMT

Can try this:

table2:

load * inline

[

POLICY_NO_new,CUSTOMER,REMARKS

789,C,NOT INTRESTED

731,D,CLOSED

123,A,SUSPENDED

];

table1:

load * inline

[

POLICY_NO,CUSTOMER

123,A

4567,B

789,C

];

NoConcatenate

final:

load *

Resident table1 where not exists(POLICY_NO_new,POLICY_NO) ;

drop table table1;

Re: Exclude data based on data

viveksingh — Fri, 12 Oct 2018 07:02:09 GMT

‌Thanks for the quick reply..

here table1 is based on lot of transformation, so cannot put load at last.

Thinking like..

table1 load first and finally to load the exclude file.

Re: Exclude data based on data

viveksingh — Fri, 12 Oct 2018 07:04:45 GMT

‌yyeah sounds goo. What if I have only POLICY_NO in the excel

Re: Exclude data based on data

jonathandienst — Fri, 12 Oct 2018 07:06:29 GMT

>>able1 load first and finally to load the exclude file.

You do need the exclude file before you filter table1.

>>here table1 is based on lot of transformation, so cannot put load at last.

Why not? Or build table1 with the transformations and then resident load the final table from there using the Not Exists condition in Liron's post above.

Re: Exclude data based on data

shreya_nadkarni — Fri, 12 Oct 2018 07:18:38 GMT

Need to rename the field in the table1 if POLICY_NO is there in excel.

Re: Exclude data based on data

viveksingh — Fri, 12 Oct 2018 08:00:28 GMT

‌if i do residen load I have to use group by where I have many number of fields.

but I think I cannot write like below

load *

resident table 1

Re: Exclude data based on data

viveksingh — Fri, 12 Oct 2018 08:56:21 GMT

‌tthanks for the reply.

BUt I have to use group by to get resident load. As I have 300 fields it is huge to group by

Re: Exclude data based on data

jonathandienst — Fri, 12 Oct 2018 10:55:24 GMT

>>if i do residen load I have to use group by

Only if you have aggregate functions like sum/count/max...

If are just reloading to apply a filter, there is no need for a group by.

Re: Exclude data based on data

arvind1494 — Fri, 12 Oct 2018 11:14:53 GMT

TRY THIS:

TO_EXCLUDE:

LOAD POLICY_NO&'_'&CUSTOMER AS KEY,

POLICY_NO,

CUSTOMER,

REMARKS

FROM

[https://community.qlik.com/thread/316649]

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

LOAD concat(chr(39)& KEY& chr(39),',') as EXCLUDE_RECORDS

RESIDENT TO_EXCLUDE;

Let vEXCLUDE = fieldvalue('EXCLUDE_RECORDS',1);

DROP Table TO_EXCLUDE;

DROP Field EXCLUDE_RECORDS;

MYRECORDS:

LOAD

POLICY_NO&'_'&CUSTOMER AS KEY,

POLICY_NO,

CUSTOMER

FROM

[https://community.qlik.com/thread/316649]

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

Where NOT WildMatch(POLICY_NO&'_'&CUSTOMER,$(vEXCLUDE));