I need to calculate the delta between two rows, for each row, taking into consideration the timestamp and for each user id.
I have these columns and I must calculate column Delta A example:
For each user_id I have to:
- sort the timestamp (with sorter node)
- Then I have to calculate the difference for the column “A” between the timestamp 2 - timestamp 1, timestamp 3 - timestamp 2 etc…
How can I do that?
The first timestamp for each user id should be 0.
Start with extracting date from the timestamp then use Group Loop start node (group is date and user ID). In the loop use Lag Column node and Math Formula node. After the Loop end node replace null values with 0 in the delta column (the first row in the group should be 0)
Thanks! I think there is something wrong in the node group loop start because If I set group as data and user id, I will obtain only one row in the entire file.
Otherwise If I set group every columns it works.
How can I do?
Have you first extracted the date from the timestamp.
Look at Select rows based on condition in precedents and followings rows … the problem is similar to yours
Yes, I have used the node Unix timestamp to date&time before the loop:
Is it correct?
You have to delete the time from the date so the Groupby node can group by date (same day).
I use Extraxt Date&Time Fields node
but you have to delete the time part
Ah ok but I need the time part because for each day and month I will have several records and I can distinguish them only from the time.
Otherwise, without the time, how can you identify which date is before or after one respect to another?
You extract the date in new column the original will remain the same
Can you upload the excel file
Here the excel:
example.xlsx (18.9 KB)
I delete time from the date&time but it does not work: in the loop start node I always see one single row.
Here is a basic example on which you can work on
giad.knwf (42.6 KB)
But I have two problems:
- I Have “?” values in rta(-1). Why? I need all values
- It seems it does not sort for time because if you see for example in column Delta A the value = -18, the time is at 10:04:41, and it is not after the previous one at 12:43:52… How can I sort for time?
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.