I need your help again. I have few fields in a dataset like below:-
PSD | PED | EXF PRICE | VN EXF PRICE | HOSP PRICE | VN HOSP PRICE | PPP | Launch Date | EXF Less Discount | PPP Less Discount | HOSP Less Discount | SUD | RI DATE |
15/09/2017 | 31/10/2018 | 275.26 | 255.99 | - | - | 284.63 | - | 275.26 | 284.63 | - | 25/12/2018 | - |
01/10/2017 | 31/12/2018 | 275.2 | 255.94 | - | - | 284.57 | - | 275.2 | 284.57 | - | 02/01/2019 | 01/12/2018 |
01/10/2017 | 04/12/2018 | 235.37 | 218.89 | - | - | 243.48 | - | 235.37 | 243.48 | - | 25/12/2018 | 23/11/2019 |
Also, I have a reference file as:-
Price1 | Price2 | Date1 | Date2 | |
EXF PRICE | EXF Less Discount | SUD | PED | |
EXF Less Discount | VN EXF PRICE | PED | PSD | |
HOSP PRICE | HOSP Less Discount | PSD | RI DATE | |
HOSP Less Discount | VN HOSP PRICE | RI DATE | Launch Date | |
PPP | PPP Less Discount |
What I want to do is, to check if the Price1 > Price2 and Date1 > Date2 (List of Price and Date is to be considered from the reference file)
Also, if Price2 is undefined (-) in data and the same field is used as Price1 on the next row then the original Price1 will be compared with the new Price2. For example, if EXF Less Discount is "-" then EXF PRICE must be greater than or equal to VN EXF PRICE. And the same logic I want to implement for Dates as well.
It would be a great help if anyone could suggest some ways to do this.
Solved! Go to Solution.
Can anyone please help me with this?
Hello @messi007
I will breakdown the problem to a smaller part.
Given the below data:-
PSD | PED | EXF PRICE | VN EXF PRICE | HOSP PRICE | VN HOSP PRICE | PPP | Launch Date | EXF Less Discount | PPP Less Discount | HOSP Less Discount | SUD | RI DATE |
15/09/2017 | 31/10/2018 | 275.26 | 280 | - | - | 284.63 | - | 275.26 | 284.63 | - | 25/12/2018 | - |
01/10/2017 | 31/12/2018 | 275.2 | 284 | - | - | 284.57 | - | - | 284.57 | - | 02/01/2019 | - |
01/10/2017 | 04/12/2018 | 235.37 | 218.89 | - | - | 243.48 | - | 235.38 | 243.48 | - | 25/12/2018 | - |
I just want to check three conditions -
1.Whether the prices follows the hierarchy :- EXF PRICE >= EXF Less Discount Price >= VN EXF PRICE
2. If any one of the price level (EXF PRICE, EXF Less Discount Price, VN EXF PRICE) is not present "-" then the other two price levels should be checked for hierarchy, for example, if EXF Less Discount Price is "-" for any record (here in Row 2) then it should compare if EXF PRICE >= VN EXF PRICE
Here, in the given data, all 3 rows should give you a false result in output-
Price compare | PSD | PED | EXF PRICE | VN EXF PRICE | HOSP PRICE | VN HOSP PRICE | PPP | Launch Date | EXF Less Discount | PPP Less Discount | HOSP Less Discount | SUD | RI DATE |
FALSE | 15/09/2017 | 31/10/2018 | 275.26 | 280 | - | - | 284.63 | - | 275.26 | 284.63 | - | 25/12/2018 | - |
FALSE | 01/10/2017 | 31/12/2018 | 275.2 | 284 | - | - | 284.57 | - | - | 284.57 | - | 02/01/2019 | - |
FALSE | 01/10/2017 | 04/12/2018 | 235.37 | 218.89 | - | - | 243.48 | - | 235.38 | 243.48 | - | 25/12/2018 | - |
I hope this explanation helps.
Please see below:
Attached the workflow,
If this solves your issue please mark the answer as correct, it will help others!
Regards
Thanks @messi007
It works. But if I have more set of fields for which I need to do this, is repeating all these steps is the only option? Or, is there a way I can give reference of the fields for which I need to implement this and it will repeat the same steps for all fields present in the reference list?