Table A has a column, say [Price].
Table B also has a price column.
I want to get the value of price from table B which is just higher than the price value in table A.
Meaning I want to get the next large price value.
There can be multiple next higher price of the same value, which will return a list in this case I suppose.
Though I can think of it in terms of logic, I’m having a hard time putting it into an appropriate expression.
Please help.
Would you please give a sample data so other community users are able to help you with your challenge, thanks.
I have solved it.
I had actually asked solution for part of the whole problem - it felt more appropriate to ask solution for a boiled down part rather than asking for the solution as a whole.
But now that I have solved it, I will pose the entire problem here. To be honest, this is one of the most interesting pieces I have worked on.
The requirment:
A table holds height, width and price values for doors. Those are the only three columns in the table beside the key column.
There’s another table called ‘quote’, the form of which would be used to enter and prepare quotes for a customer to send to. The customer will pose his/her requirement of door in dimensional value, and the price to be calculated is for the door whose height and width is just greater than the values which have been entered upon customers request.
This is the table (part of):
Lets say the customer wants his door to be 4300 X 2290
Then the price would be for the door from the table - 4450 X 2440 as they are the next closet values. The corresponding price is 1158
How I achieved it:
I made virtual columns, say [Next width key] and [Next Height Key] in the quote table with formulas:
minrow(“Sectional doors”, “Width”, ([Width] > [_THISROW].[Width]))> minrow(“Sectional doors”, “Height”, ([Height] > [_THISROW].[Height]))
These return the key values of rows from the “Sectional doors” table with height and width just greater than that entered.
Then I used dereference to pull the numeric values of the height and width, storing these in virtual columns too:
[Next Width key].[Width]> [Next Height key].[Height]
Then finally I used a select expression to calculate the price :
any(> select(> Sectional doors[Price], and([Width]=[_THISROW].[Next Width], [Height]=[_THISROW].[Next Height])> )
This gives me the price of the door with the next closest dimensions.
Nicely done!
