I'm trying to roundup values to a specific set of values specified in a secondary file. I can do it by writing a calculation like
If [value field] <= 50, then 50
if [ value field] <= 100, then 100 etc.
However, in different countries, and across different years these brackets may be different. So I'd like a sustainable solution that relies on actual input data as to what brackets exist, instead of hard coding these in a calculation.
How would you solve this in omniscope?
As example I've added an example set of 5 records with a KG value that requires grosses up,
The specific weight brackets are the right of the example records.
Here is one solution - it requires an Inequality join block (with partition on Country) - then you can join the invoice kg value with all the bracket values that are equal or higher.
(partition will ensure this is done on a per country basis)
Inequality block is a Custom block and it will require R installation, if you haven't done that already. If not - instructions are on the block's Setup tab.
Follow this with an Aggregate block, to pick the lowest Bracket value per invoice.
I can do it by writing a calculation like
So I'd like a sustainable solution that relies on actual input data as to what brackets exist, instead of hard coding these in a calculation.
Kay
Attachments (1)
Brackets.xlsx
12.4 KB
0 Votes
1 Comments
Paola Tomei posted over 2 years ago Admin
(partition will ensure this is done on a per country basis)
Inequality block is a Custom block and it will require R installation, if you haven't done that already. If not - instructions are on the block's Setup tab.
0 Votes
Login or Sign up to post a comment