topic Re: Load Where exists in Resident Table in QlikView

Load Where exists in Resident Table

prees959 — Thu, 07 Jun 2018 12:24:06 GMT

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

Re: Load Where exists in Resident Table

Frank_Hartmann — Thu, 07 Jun 2018 12:49:54 GMT

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;

Re: Load Where exists in Resident Table

Frank_Hartmann — Thu, 07 Jun 2018 13:29:36 GMT

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

Re: Load Where exists in Resident Table

lcontezini — Thu, 07 Jun 2018 13:37:52 GMT

Try this:

UNIT_TABLE:

LOAD

Unit

From table_name_1;

AREAS_TABLE:

LOAD

Unit,

Area

From table_name_2

Where exists(Unit);

Re: Load Where exists in Resident Table

wenchia — Thu, 07 Jun 2018 15:28:40 GMT

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;