Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rido1421
Contributor III

Lookup() Function

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

1 Solution

Accepted Solutions

Re: Lookup() Function

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);


Capture.PNG

9 Replies
MVP & Luminary
MVP & Luminary

Re: Lookup() Function

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

rido1421
Contributor III

Re: Lookup() Function

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 ...

    

NameOfCarClassPerformanceSalesValue
2009 bmW 325iAExcellent5000
Toyota Corolla 1.3CBad1000
Volvo S4AGoodN/A
Mini CooperBAverageN/A
Kia PicantoCBad2000

Your assistance is appreciated.

Re: Lookup() Function

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);


Capture.PNG

rido1421
Contributor III

Re: Lookup() Function

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

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

rido1421
Contributor III

Re: Lookup() Function

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

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

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);

rido1421
Contributor III

Re: Lookup() Function

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.