Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to do lookup() in script load with no exact match

Hi Community,

Hope you can help me with next:

I have a facts table with unique numbers, points and a rent value.

Based on that I joined a table to get max rent per point (see bottom left), that's OK:)


BUT, now I would like to determine the other way: max points based on given rent.

My problem is that the rent has not an exact match with the joined table.


Can you help me to solve this?

In excel the solution is a Vlookup function with TRUE as 4th argument.

 

Max rent per point.PNGPreview.PNG

Thanks in advance! Gr Pascal

3 Replies
bwisealiahmad
Partner - Specialist
Partner - Specialist

Hi,

I think your best alternative is using a mapping table. This can be done with the ApplyMap function.

Read more about ApplyMap here: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/MappingFunctions/ApplyMa...

Here is a YouTube video referring to that Excel function you are trying to do and solving it with ApplyMap and LookUp function.

https://www.youtube.com/watch?v=QIzyXWBtFnk

Best,

Ali A

rubenmarin

Hi, yo can add to your points table the min rent for each points to create and interval and use intervalmatch to assign the Points:

Rents:

LOAD * inline [

Rent

13

25

90

];

Points:

LOAD * INLINE [

    Points, Max Rent

    40, 12.21

    50, 24.35

    60, 35.25

];

PointInterval:

LOAD

    If(IsNull(Peek([Min Rent])), 99999, Peek([Min Rent])-0.001) as [Max Rent],

    [Max Rent] as [Min Rent],

    [Points]

resident Points order by Points desc;

DROP Table Points;

Inner Join IntervalMatch ( Rent )

LOAD [Min Rent], [Max Rent]

Resident PointInterval;

jonathandienst
Partner - Champion III
Partner - Champion III

You could use an Interval match here.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein