topic Re: exclude common records in QlikView

exclude common records

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

i have a Table A having 100 ID,

Table B is having 20 ID

my requirement is Table C which should not have 20 ID from Table B

how to acheive this?

Re: exclude common records

keerthika — Mon, 16 Apr 2018 11:33:45 GMT

Hi Sanju,

Do you want to omit these 20 IDs from Table B even if they are present in Table A?

Keerthika

Re: exclude common records

sanjujeeboy — Mon, 16 Apr 2018 11:34:18 GMT

Yes

Re: exclude common records

Peter_Cammaert — Mon, 16 Apr 2018 11:34:30 GMT

TableA:

// stuff for table A

TableB:

// stuff for table B

MapTableB:

MAPPING LOAD ID, false() as Flag RESIDENT TableB;

TableC:

LOAD * FROM TableC-Source (options)

WHERE ApplyMap('MapTableB', ID, true());

Best,

Peter

[Edit] Corrected. You want to end-up with 3 tables.

Re: exclude common records

prasad_b — Mon, 16 Apr 2018 11:36:09 GMT

Can u please provide sample data..so it will be easy to solve your problem.

You can use where exist function for same

Re: exclude common records

dathathreya — Mon, 16 Apr 2018 11:41:00 GMT

Plz eloborate this as u mentioned that table A having 100 ID's and Table B having 20 ID's ,where in Table C u want to show 100 ID's of A and to exclude Table B means u can use Residant load for Table c.

Re: exclude common records

ahmar811 — Mon, 16 Apr 2018 11:45:31 GMT

hi,

If you have table C from separate/different source you can use below script :

TableA:

// stuff for table A

TableB:

// stuff for table B

TableC:

LOAD * FROM TableC-Source (options)

WHERE NOT EXITS(ID);

If you have table C from table A you can use below script :

TableA:

// stuff for table A

TableB:

// stuff for table B

NoConcatenate

TableC:

LOAD * RESIDENT TableA

WHERE NOT EXITS(ID);

I hope this will help you

Regards

Ahmar

Re: exclude common records

sanjujeeboy — Mon, 16 Apr 2018 11:47:49 GMT

THE 20 ids FROM TABLE B ARE TO BE REMOVED FROM TABLE A

Re: exclude common records

sanjujeeboy — Mon, 16 Apr 2018 11:48:01 GMT

THE 20 ids FROM TABLE B ARE TO BE REMOVED FROM TABLE A

Re: exclude common records

ahmar811 — Mon, 16 Apr 2018 11:51:11 GMT

Please consider my replay in that second scenario will help you in that we will exclude ID which are already present in Table B

Re: exclude common records

Peter_Cammaert — Mon, 16 Apr 2018 12:38:02 GMT

Your latest comment makes your requirement a bit more clear.

How about this:

MapTableB:

MAPPING LOAD ID, false() as Flag RESIDENT TableB;

TableA:

LOAD * FROM TableA-Source (options)

WHERE ApplyMap('MapTableB', ID, true());

This will load all rows from the TableA data source except those presejnt in TableB (without loading TableB). If TableA is already present in your data model, transfer it to a new RESIDENT table using this technique, drop the first TableA and rename the second table to TableA.