Performing look up on range data

[spyglass]

I have two datasets, one contains configuration settings, that is Distance in ranges and respective price, other dataset contains billed amount as per distance covered.

I want to perform look up and retrive price from configuration file to billed dataset and find differences in price.

Here’s configuration file
configuration.xlsx (10.3 KB)

Here’s bill dataset
data given.xlsx (1006.3 KB)

Here’s desired output
AnalysisResults.xlsx (1.4 MB)

[ArjenEX]

Hi @spyglass

Something like this should get you started:

Some pointers:
The highest number of each range is a multiplication of 20. With this in mind, you can find the nearest “20” of the distance_travel by doing ceil($distance_travel$/ 20) * 20 in a Math node. Split the range into two columns to be able to perform a join later on.

The first range in the configuration file is a bit odd since it’s covers 100 numbers instead of 20. Since there are distances travelled that are lower than 80 (the last range of 20 numbers before 100), these need to be corrected.

One way to correct this is by using a Column Expression:

if (column("upper_item") < 100) {
    100
} else {
    column("upper_item")
}

You can then left outer join the configured cost.

This makes the difference calculation an easy one to do

See WF: Performing look up on range data.knwf (1.3 MB)

Hope this helps!