Load Where exists in Resident Table

[prees959]

Hi,

I have a resident table with two rows:

Unit

Unit A

Unit B

I need to load all rows from an external table "Areas" where Unit is matched :

Areas | Unit

Area 1,Unit A

Area 2,

Area 76, Unit A

Area 53, Unit B

Area 7, Unit A

Area 10,

Area 12, Unit D

So my resulting Areas table should hold

Area 1,Unit A

Area 76, Unit A

Area 53, Unit B

Area 7, Unit A

Thanks for any advice,

Phil

[Frank_Hartmann]

Maybe like this:

tmp1:
LOAD * INLINE [
    Unit, Areas
    UnitA,
    UnitB,
];

Map:
Mapping Load * Resident tmp1; DROP table tmp1;

tmp2:
LOAD * INLINE [
    Areas,  Unit
    Area1,  UnitA
    Area2,  UnitC
    Area76, UnitA
    Area53, UnitB
    Area7,  UnitA
    Area10, UnitF
    Area12, UnitD
];

NoConcatenate

final:
Load *,
Applymap('Map',Unit,'Null') as Unit_New Resident tmp2 Where Applymap('Map',Unit,'Null')<>'Null';DROP table tmp2;

[Frank_Hartmann]

Add the Field "Areas" in tmp1 Table even if there is no content in it.

Mapping Load only works if at least two fields are availabel

So just make a preceeding load like:

tmp1:

Load *, '' as Areas;

Load Unit

From Yourtable;

Map:

Mapping Load

etc.....

hope this helps

[lcontezini]

Try this:

UNIT_TABLE:

LOAD

     Unit

From table_name_1;

AREAS_TABLE:

LOAD

     Unit,

     Area

From table_name_2

Where exists(Unit);

[wenchia]

UNIT_TABLE:

LOAD

     Unit

From table_name_1;

AREAS_TABLE:

LOAD

     Unit,

     Area

From table_name_2

Where exists(Unit,Unit);

drop table UNIT_TABLE;