Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think the trick would be to generate an upper bound in the Example table and then use IntervalMatch(). Like this:
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
];
I think the trick would be to generate an upper bound in the Example table and then use IntervalMatch(). Like this:
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];