lookup within tables and result sets

[marko_rakar]

I have following situation:

"Master table":

LOAD * INLINE [

    ID, Name

    3021, B

    3031, C

    3051, D

    3061, G

];

"Table A":

LOAD * INLINE [

    ID, Property_1

    3021, B

    3031, C

    3051, D

    3061, G

];

"Table B":

LOAD * INLINE [

    ID, Property_2

    3021, A

    3031, F

    3051, M

    3061, X

];

"Table for lookup":

LOAD * INLINE [

    ID, Lookup_Property

    3021, B

    3031, F

    3051, L

    3061, T

];

My idea is following;

I want to find all instances of ID where value set in field Lookup_Property does not show in fields Property_1 or Property_2.

So, in this particular case, my result set should include IDs 3051 and 3061 (in the case of ID 3021 "B" shows up in Table A and in the case of ID 3031 it shows up in Table B)

[SunilChauhan]

"Table for lookup":

Load * from table name;

"Table A":

load *

from table name where not Exists(Name,Property_1);

Concatenate

"Table B":
Load * from tablename where not Exists(Name,Property_2);

drop table "Table for look up";

apply this in ur code

hope this helps

[Peter_Cammaert]

Concatenate Table A and Table B and turn it into a mapping table like:

MergedTable:

LOAD ID & '-' & Property_1 AS Index, 1 AS Flag

RESIDENT [Table A];

CONCATENATE (MergedTable)

LOAD ID & '-' & Property_2 AS Index, 1 AS Flag

RESIDENT [Table B];

AvoidThese:

MAPPING LOAD * RESIDENT MergedTable;

DROP Table MergedTable;

Now when transferring data from one place to another (e.g. from [Table for lookup] to somewhere else) use applymap() to check whether we should really keep the current record.

FilteredTable:

NOCONCATENATE LOAD *

RESIDENT [Table for lookup]

WHERE IsNull(applymap('AvoidThese', ID & '-' & Lookup_Property, NULL());

There ar eother solutions as well.

Peter