Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
"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
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