Calculating number of days for different actions in load script (or a better way?)

[rmadursk]

I have a set of data that looks like this:

Name	Date	Action
peter	1/1/2025	open
paul	1/1/2025	close
mary	1/1/2025	reject
peter	1/2/2025	open
paul	1/3/2025	close
mary	1/4/2025	reject
peter	1/5/2025	reject
paul	1/6/2025	open
mary	1/7/2025	close
peter	1/8/2025	open
paul	1/9/2025	close
mary	1/10/2025	reject

I want to have results that look like this:

Name	Date	Action	results
peter	1/1/2025	open	0
paul	1/1/2025	close	0
mary	1/1/2025	reject	0
peter	1/2/2025	open	1
paul	1/3/2025	close	2
mary	1/4/2025	reject	3
peter	1/5/2025	reject	0
paul	1/6/2025	open	0
mary	1/7/2025	close	0
peter	1/8/2025	open	6
paul	1/9/2025	close	6
mary	1/10/2025	reject	6

I've been trying to do it with concatenated fields, nested IF statements and multiple variables and I'm having no luck getting the syntaxes correct to do so, and it is making the script way too complicated to be readable. 

The results are just the number of days since the named person has performed a particular action.

I could probably do this in Excel or a Python/PowerShell script prior to loading the data but I'd really like to understand how it can be done with the data load script. In my real data I have about 500 Names and a dozen or so actions, but I don't believe that will matter to the solution.

[pravinboniface · Accepted solution]

@rmadursk Can you try this:  Change the sort order to be sorted by Name, Action and Date.  Then it's a simple action to check the previous row.  If the name and action matches, calculate the difference in the number of days.  If there is no match, use 0.

Temp:
load Name, Date(Date) as mydate, Action Inline [
Name Date Action
peter 1/1/2025 open
paul 1/1/2025 close
mary 1/1/2025 reject
peter 1/2/2025 open
paul 1/3/2025 close
mary 1/4/2025 reject
peter 1/5/2025 reject
paul 1/6/2025 open
mary 1/7/2025 close
peter 1/8/2025 open
paul 1/9/2025 close
mary 1/10/2025 reject
] (delimiter is ' ');

sorted_data:
NoConcatenate
load Name,mydate,Action,
if (Previous(Name)=Name and Previous(Action)=Action, mydate-Previous(mydate),0) as NoOfDays
Resident Temp
order by Name,Action,mydate;

drop table Temp;
exit script;