I have a challenge that I thought it was going to be easy, but I definitely was not able to come up with the solution.
There was a price change for a material where the initial price was $16.8 per case with price valid from 1/1/2011 and good until 3/31/2018. With the price change, starting on 4/1/2018, each case will now cost $20. So, I need to make a date comparison to the price date range.
If "Forecast Month" > "Valid from" and "Forecast Month" < "Valid to", than "Price per case".
So, following this logic, based on the data below, the results should be like below:
"Forecast Month" = 3/1/2018, than "Price per case" = 16.8
"Forecast Month" = 4/1/2018, than "Price per case" = 20
Note: I attached an Excel file with this data below to make it easier if you want to try it.
First, thank you for the quick response and trying to help me figure this out.
However, I need a more dynamic solution. Maybe I have given a simple example.
I have a logic in Qlik where I pull cases and List Price per case from the system, and calculate the Dollar value, by multiplying them. However, the List Price might change once in a while, for several products, like hundreds. So, I need a logic based on the data range of the List Price, so that Qlik uses the List Price for that specific date range, Valid from and Valid to.
If "Forecast Month" (First day of the month) falls into the date range of the List Price, give me the List Price for that date range. I could pull the most current List Price, however we need to keep the old ones for historical transactions.
You would populate the data for the IntervalMatch from your database, not an inline load as in my example. You would have the historical prices still attached to records where the from/to date range was appropriate.
Thanks for your help. The IntervalMarch did the work, exactly like you mentioned before. I used a Left Join after the load, in order to create a table I could save a use it for future calculation in the script. The final script it as follows: