You can use a Python script in a transform node. The data should first be sorted by Date using a Sort node.
The Transform node code is shown below:
And the corresponding test data and results are as below:
The details of the example scripts are as follows:
####ConfigureFields Script
#Configure all fields from input 'in1' to be mapped
#to the corresponding fields on the output 'out1'
out1 += in1
out1.Delta = int
#### End ConfigureFields Script
#### ProcessRecords Script
#Copy all fields from input 'in1' to the corresponding output fields
#in output 'out1'. Copies the values of the fields that have been setup
#in the mapping defined in the ConfigureFields property
out1 += in1
if node.firstExec:
LastVal = fields.count
out1.Delta = Null
else:
if fields.count is Null:
LastVal = 0
out1.Delta = Null
else:
Diff = fields.count - LastVal
out1.Delta = Diff
LastVal = fields.count
#### End ProcessRecords Script
------------------------------
Adrian Williams
Precisely Software Inc.
------------------------------
Original Message:
Sent: 10-10-2023 05:38
From: Henrik B
Subject: Date to date delta count for time series
Hello, assume i have 3 dates and a count column per each date, is there a convenient way to calculate the delta for each 2 adjacent dates? starting from the oldest date:
(In reality it will be around 30-60 dates)
What i have:
|Date| |count|
2023-01-02 1
2023-01-03 8
2023-01-04 9
What i want:
|Date| |count| |Delta|
2023-01-02 1 NaN
2023-01-03 8 7
2023-01-04 9 1
Best regards
Henrik
------------------------------
Henrik B
E.ON Sverige
------------------------------