Date and price comparison

[Pramod91]

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.

[Pramod91]

Can anyone please help me with this?

[messi007]

@Pramod91,

Could you share the expected output it will help to understand your need 🙂

Best Regards,

[Pramod91]

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.

[messi007 · Accepted solution]

@Pramod91,

Please see below:

Attached the workflow,

If this solves your issue please mark the answer as correct, it will help others!

Regards

[Pramod91]

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?