Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content
Announcements
WEBINAR June 25, 2025: Build on Apache Iceberg with Qlik Open Lakehouse - REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
StephenDunn
Contributor III
Contributor III

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

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

Labels (1)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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.
 

View solution in original post

3 Replies
Chanty4u
MVP
MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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.
 
StephenDunn
Contributor III
Contributor III
Author

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