Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

lookup within tables and result sets

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)

2 Replies
Champion
Champion

"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