Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
viveksingh
Contributor II

Exclude data based on data

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_NOCUSTOMER
123A
4567B
789C

excel was table consider to exclude

POLICY_NOCUSTOMERREMARKS
789CNOT INTRESTED
731DCLOSED
123ASUSPENDED
Tags (1)
11 Replies
viveksingh
Contributor II

Re: Exclude data based on data

Expected result:

POLICY_NOCUSTOMER
4567B
Partner
Partner

Re: Exclude data based on data

hi

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;

Partner
Partner

Re: Exclude data based on data

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;

viveksingh
Contributor II

Re: Exclude data based on data

‌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.

viveksingh
Contributor II

Re: Exclude data based on data

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

MVP
MVP

Re: Exclude data based on data

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Exclude data based on data

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

viveksingh
Contributor II

Re: Exclude data based on data

‌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

viveksingh
Contributor II

Re: Exclude data based on data

‌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