topic Re: Lookup between values within script (Qlik on Cloud, not QlikView) in App Development

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

StephenDunn — Thu, 27 Mar 2025 16:48:02 GMT

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

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

Chanty4u — Thu, 27 Mar 2025 16:57:03 GMT

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

];

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

rwunderlich — Thu, 27 Mar 2025 19:00:39 GMT

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

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

StephenDunn — Fri, 28 Mar 2025 12:57:32 GMT

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