Lookup between values within script (Qlik on Cloud, not QlikView)

[StephenDunn]

Given a table such as this

[Example]:
Load * Inline
[LowerBound,Result
0,150
51,125
64,150
75,200
111,125
136,150
175,200
211,200
];

Is it possible (without expanding the table to include every possible value) to do a lookup, such that looking up 70.123 in this table will return 150?  The lookup functions I can find all seem to require an exact match or a string pattern.

Thanks

[Chanty4u]

Try this 

MapResults:

MAPPING LOAD

    LowerBound,

    Result

INLINE [

    LowerBound, Result

    0, 150

    51, 125

    64, 150

    75, 200

    111, 125

    136, 150

    175, 200

  211, 200

];

Data:

LOAD

    Value,

    ApplyMap('MapResults', Floor(Value / 50) * 50, Null()) AS MappedResult

INLINE [

    Value

    70.123

    120

    160

    210

];

[rwunderlich · Accepted solution]

I think the trick would be to generate an upper bound in the Example table and then use IntervalMatch(). Like this:

[Example]:

Load *

Inline [

LowerBound,Result

0,150

51,125

64,150

75,200

111,125

136,150

175,200

211,200

];

Join (Example)

Load 

  LowerBound,

  Peek('LowerBound', RecNo(), 'Example') as UpperBound

Resident Example;

Data:

Load Value

INLINE [

    Value

    70.123

    120

    160

    210

];

Left Join(Data)

IntervalMatch (Value)

LOAD LowerBound, UpperBound 

Resident Example;

That will leave you with a Syn key that you can get rid of depending on what your actual model looks like.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[StephenDunn]

Thanks rwunderlich, I should have taken more time reading IntervalMatch documentation!
Just a little bit of tweaking gave exactly what I needed.

Set boundaryTolerance = 0.0001;

[OutputVals]:
Load * Inline
[LowerBound,Output
0,150
51,125
64,150
75,200
111,125
136,150
175,200
1000000
];

[BoundsMap]:Mapping Load [LowerBound],[Output] Resident [OutputVals];

[InputVals]:
Load * Inline
[Input
0
3
48
50
53
64
67
70.123
120
160
210
];

// IntervalMatch adds the correct LowerBound and UpperBound values against the Input values
Left Join([InputVals])
IntervalMatch ([Input]) Load
[LowerBound],
Peek('LowerBound', RecNo(), 'OutputVals')-$(boundaryTolerance) as [UpperBound]
Resident [OutputVals];

// Now mapping can be applied to those LowerBound values
Left Join([InputVals]) Load [LowerBound], ApplyMap('BoundsMap',[LowerBound]) As [Output] Resident [OutputVals];

Drop Table [OutputVals];
Drop Fields [LowerBound],[UpperBound];