topic Re: Lookup() Function in QlikView

Lookup() Function

rido1421 — Thu, 23 Jun 2016 12:09:36 GMT

Hi there

Is there a way to use the lookup function to lookup an exact field within a wildcard field.

in the example below I would like to return the sales value for the CarName='Bmw' in the Cars table the NameofCar='2009 bmW 325i'

This is the logic:

Lookup('fieldname','matchfieldname',matchfieldvalue,'tablename')

Lookup('Salesvalue','CarName',*NameofCar*,'Sales')

I would like to make my search dynamic

Thank you

Re: Lookup() Function

marcus_sommer — Thu, 23 Jun 2016 13:35:30 GMT

I don't think that this will be possible and I doubt that these approach makes much sense then lookup will always return the first matching. Maybe it's better to map/join/associate those tables over a field like "CarName".

- Marcus

Re: Lookup() Function

rido1421 — Fri, 24 Jun 2016 05:59:29 GMT

Hi Marcus

Thank you for your reply

This is basically what I am trying to do. with the attached data

I would like to return the below table basically get the sales value for each NameOfCar from Sales table

The condition is, As long as NameOfCar from the Cars table contains the CarName from the Sales table, then return the Sales value for car name in line with NameOfCar in the Cars table.

How would we do this in Qlikview? in excel we could do a vlookup ...

NameOfCar	Class	Performance	SalesValue
2009 bmW 325i	A	Excellent	5000
Toyota Corolla 1.3	C	Bad	1000
Volvo S4	A	Good	N/A
Mini Cooper	B	Average	N/A
Kia Picanto	C	Bad	2000

Your assistance is appreciated.

Re: Lookup() Function

sunny_talwar — Fri, 24 Jun 2016 06:10:11 GMT

Try this code:

MappingSales:

Mapping

LOAD Upper(CarName),

'/' & SalesValue & '\'

FROM

Cars.xlsx

(ooxml, embedded labels, table is Sales);

Cars:

LOAD NameOfCar,

Class,

Performance,

If(Len(Trim(TextBetween(MapSubString('MappingSales', Upper(NameOfCar)), '/', '\'))) = 0, 'N/A',

TextBetween(MapSubString('MappingSales', Upper(NameOfCar)), '/', '\')) as SalesValue

FROM

Cars.xlsx

(ooxml, embedded labels, table is Cars);

Re: Lookup() Function

rido1421 — Fri, 24 Jun 2016 06:41:27 GMT

Thank you Sunny this would be Ideal, Im trying it in my actual model doesn't seem to be working for now im just going to try and tweak it some more I will let you know shortly

Re: Lookup() Function

sunny_talwar — Fri, 24 Jun 2016 06:43:00 GMT

Sure thing.... just make sure that your mapping table has only two columns.

Re: Lookup() Function

rido1421 — Fri, 24 Jun 2016 07:18:30 GMT

Thank You will do, in my actual model I am returning a textname from the naming table instead of a sales value.

For some reason in some cases it is returning a portion of "NameOfCar" field

in some cases it returns "/" before the result

and in a small amount of cases it returns only "/" ,

In my actual "NameOfCar" field the data does contain "/"

Sorry about this I am still trying to understand what the code does.

Re: Lookup() Function

sunny_talwar — Fri, 24 Jun 2016 07:21:17 GMT

If you NameOfCar field includes / or \, then it might become problematic. Use a special character which would never appear in your NameOfCar field

MappingSales:

Mapping

LOAD Upper(CarName),

'/' & SalesValue & '\'

FROM

Cars.xlsx

(ooxml, embedded labels, table is Sales);

Re: Lookup() Function

sunny_talwar — Fri, 24 Jun 2016 07:26:16 GMT

and make that same change here as well:

Cars:

LOAD NameOfCar,

Class,

Performance,

If(Len(Trim(TextBetween(MapSubString('MappingSales', Upper(NameOfCar)), '/', '\'))) = 0, 'N/A',

TextBetween(MapSubString('MappingSales', Upper(NameOfCar)), '/', '\')) as SalesValue

FROM

Cars.xlsx

(ooxml, embedded labels, table is Cars);

Re: Lookup() Function

rido1421 — Fri, 24 Jun 2016 07:37:58 GMT

Thank You Sunny,It seems to work nicely with %, I will test it some more to ensure there is no other issues. You are indeed a Legend.