topic Re: Lookup Function in QlikView

Lookup Function

Mon, 07 Jul 2014 16:34:36 GMT

Hi All,

I want to apply lookup and check if field is already added in above table. Please see the details below.

LOAD * INLINE [
Cityname
Delhi,
Pune,
Bihar,
];

Directory;
LOAD place,
' here i want to add new field yes or no. if place exists in citynames(in above table) return yes else no
FROM
[..\..\sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Can anyone please help me to add the expression in load syntax?

Re: Lookup Function

nilo_pontes — Mon, 07 Jul 2014 16:40:43 GMT

Hi Sunny,

Try: If(exists(Cityname, place)=-1,'Yes','No') As FieldName

Best Regards,

Nilo

Re: Lookup Function

its_anandrjs — Mon, 07 Jul 2014 17:09:52 GMT

Try to load your table like see below small example

T1:

LOAD * INLINE [

Cityname

Delhi,

Pune,

Bihar ];

T2:

LOAD * Inline [

place

Delhi

Pune

India ];

New:

LOAD

Cityname as City_name

Resident T1;

Inner Join

LOAD

place as City_name,

'Y' as Flag

Resident T2;

Concatenate

LOAD

place as City_name,

'N' as Flag

Resident T2 Where Not Exists(City_name,place);

Then you get output as

Hope this helps

Re: Lookup Function

martynlloyd — Mon, 07 Jul 2014 17:28:00 GMT

Nilo has it!

Re: Lookup Function

Wed, 09 Jul 2014 15:47:12 GMT

hi Nilo,

Many thanks for your help.

Is it possible to get the count as well?

Re: Lookup Function

nilo_pontes — Wed, 09 Jul 2014 16:37:00 GMT

Hi Sunny, you're welcome.

Do you want count how many 'yes'es and 'no's you've got after load?

In this case you can create a pie chart or a straight table and count(FieldName).

Regards,

Nilo