I'm trying to rewrite an sql user defined function in a qlikview document. The user defined function does as little as take a price as a parameter and rounds it up or down to the nearest price in a list of accepted end consumer retail prices. The udf does this through simple logic, calculate the difference between param. and every one of the accepted prices, pick the lowest difference negative and the lowest difference positive. Check which difference is the smallest and give this answer back.
I want to calculate this accepted price on the fly in qlikview for several reasons but... i don't have a clue where to start. I tried the general numeric functions but no help there. Perhaps the problem is also that the list of accepted prices is not related whatsoever to the articles and prices which are in the charts allready.
There MUST be a simpler expression, but I'm guessing that this would work (assuming the AcceptedPrice is either a variable or a field unrelated in any way to the EnterePriced, and that there is only a single AcceptedPrice list, and not different lists for every product, for instance):
There MUST be a simpler expression, but I'm guessing that this would work (assuming the AcceptedPrice is either a variable or a field unrelated in any way to the EnterePriced, and that there is only a single AcceptedPrice list, and not different lists for every product, for instance):
This is the exact thing i was looking for, it works like a charm.
With less than 200 accepted prices and a total pricelist length of less than 5000 prices and less than 5 pricelists to be calculated also the performance is still in good shape.
