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.
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: