I need urgent help in calculating the price validity dates.
I have a product with multiple missing validity dates for its price. I need to follow the below logic to find the actual validity date for the price.
If SAP column already has valid from and valid till dates then just copy them to Actual valid from and Actual Valid till columns
If SAP Valid from and SAP valid till is empty but there is only one price then Actual valid from is July 2018 and Actual valid till is jun 2019
If SAP Valid from and valid till are empty but there are multiple prices then first Actual valid from is June but the valid till should be one day before the next price valid from date.
In case 3 if valid from of the second price is missing, it should be calculated by taking the first month from the quarter column
Quarter column full forms
JAS - july aug sep
OND - October november december.
Please find the attached workbook to see the problem and expected solution.dateCalc.xlsx (13.8 KB)
I am really looking forward for your reply. since I need to present the knime model to the team on friday.