Check difference between dates for a common value

[Pramod91]

Hello everyone,

I need your help to deal with some dates in a workflow. I have the data like this -

Record ID	Code	PSD	PED
1	A4356	23-08-2017	11-04-2018
2	A4356	12-04-2018	17-01-2019
3	A4356	18-01-2019	28-05-2020
4	A4356	01-06-2020	-

Here, I need to check if the PED for each row of a Code (column 2) is 1 day earlier (-1) than the PSD of the next row for that Code. And if the difference between PED of current row and PSD of the next row for a given Code is not 1 then I should  be able to find it. (Here the record ID - 3 is an example of error I want to trace).

Can someone please help me with this?

[atcodedog05 · Accepted solution]

Hi @Pramod91 

Here is a workflow for the task.

Formula:

DateTimeDiff(
DateTimeParse([Row+1:PSD],"%d-%m-%Y"),
DateTimeParse([PED],"%d-%m-%Y"),
"days")

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Emil_Kos]

Hi @Pramod91,

@atcodedog05 solution is cleaner as I don't know the regex but since I started to work on it I will share my solution.

The most important thing is combination of date time diff formula which will allow you to identify the time difference between two dates + multi row formula which allows us to perform actions on two or more rows at the same time.

I would also suggest to keep in mind that you need to sort your data correctly to make sure that everything is working correctly. 

[Pramod91]

Hi @atcodedog05  and @Emil_Kos 

It works. But as I want it to calculate only if the local code (code) is same for two consecutive rows, I combined it with the IF formula like this - IF [Row+1:Local Code]=[Local Code] THEN DateTimeDiff(
DateTimeParse([Row+1:Price Start Date],"%y-%m-%d"),
DateTimeParse([Price End Date],"%y-%m-%d"),
"days") ELSE "-" ENDIF

But it gives me an error "The formula "Days" resulted in a string but the field is numeric. Use ToNumber(...) if this is correct." 

How should I deal with this?

[atcodedog05]

Hi @Pramod91 

2 possible solution. 

1. Change the datatype of days column to string in multirow formula tool.

or

2. Replace "-" to Null() in else part.

IF [Row+1:Local Code]=[Local Code] THEN DateTimeDiff(
DateTimeParse([Row+1:Price Start Date],"%y-%m-%d"),
DateTimeParse([Price End Date],"%y-%m-%d"),
"days") ELSE "-" ENDIF

to

ELSE Null() ENDIF

[Pramod91]

Thanks for all your help, it really worked!

[atcodedog05]

Happy to help 🙂 @Pramod91 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[Pramod91]

Hi @atcodedog05,

Thanks for your solution yesterday.

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/10/2017	04/12/2018	235.37	218.89	-	-	243.48	-	235.37	243.48	-	25/12/2018	-
01/10/2017	14/10/2017	1012.39	941.52	-	-	1044.98	-	-	-	-	15/10/2017	10/12/2017
01/10/2017	14/10/2017	1302.48	1211.31	-	-	1340.98	-	-	-	-	15/10/2017	-
01/10/2017	04/12/2018	275.18	255.92	-	-	284.55	-	275.18	284.55	-	25/12/2018	-
15/09/2017	31/10/2017	279.25	259.7	-	-	288.75	-	-	-	-	01/11/2017	-
01/10/2017	31/12/2018	235.35	218.88	-	-	243.46	-	-	-	-	15/12/2018	-
15/10/2017	04/12/2018	279	259.47	-	-	288.49	-	279	288.49	-	25/12/2018	-
15/10/2017	30/11/2018	1297.5	1206.67	-	-	1336	-	-	-	-	15/12/2018	-
15/10/2017	31/10/2017	1011.44	940.64	-	-	1044	-	-	-	-	01/11/2017	-
15/10/2017	04/12/2018	1011.62	940.81	-	-	1044.19	-	1011.62	1044.19	-	25/12/2018	-
15/10/2017	04/12/2018	1298.59	1207.69	-	-	1337.09	-	-	-	-	15/12/2018	14/09/2016
01/11/2017	31/12/2018	235.34	218.87	-	-	243.45	-	235.34	243.45	-	02/01/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 you could suggest some ways to do this.

Thank you in advance!

[atcodedog05]

Hi @Pramod91 

Sorry to inform you that currently I am bit occupied. I would highly recommend you to post it as new question so that it becomes visible as new unsolved question and people can help you out.