Exclude data based on data

[viveksingh]

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

[viveksingh]

Expected result:

‌

POLICY_NO	CUSTOMER
4567	B

[lironbaram]

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;

[shreya_nadkarni]

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]

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

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

[jonathandienst]

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

[shreya_nadkarni]

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

[viveksingh]

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

‌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