I need your help again. I have few fields in a dataset like below:- PSDPEDEXF PRICEVN EXF PRICEHOSP PRICEVN HOSP PRICEPPPLaunch DateEXF Less DiscountPPP Less DiscountHOSP Less DiscountSUDRI DATE15/09/201731/10/2018275.26255.99--284.63-275.26284.63-25/12/2018-01/10/201731/12/2018275.2255.94--284.57-275.2284.57-02/01/201901/12/201801/10/201704/12/2018235.37218.89--243.48-235.37243.48-25/12/201823/11/2019 Also, I have a reference file as:- Price1Price2 Date1Date2EXF PRICEEXF Less Discount SUDPEDEXF Less DiscountVN EXF PRICE PEDPSDHOSP PRICEHOSP Less Discount PSDRI DATEHOSP Less DiscountVN HOSP PRICE RI DATELaunch DatePPPPPP 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.

Date and price comparison

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.

Workflow

Date Time

Accepted answers

messi007

@Pramod91,

Please see below:

Attached the workflow,

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

Regards

Checks20201216.yxmd

All comments

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

@Pramod91,

Please see below:

Attached the workflow,

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

Regards

Checks20201216.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703