Sure!
In my database I have rows with date and hour and IDs. The IDs represent contacts, so they can appear multiple times, but the date/hour should not be the same (because they are called several times but not at the same time).
What I needed first was to extract the number of times the IDs appear in the database, but not a normal count, a sequential one. Something like this:
To do this I followed an example by @armingrudd in here: For each row, count number of previous occurances - #7 by StevenFrancis
First, I sorted by ID and then by date. After that, I set the counter generation with min value 0 and scale unit 1 and ranked the count by ascending order grouping by ID. That gave me the first table.
My next step was to know which one is the first ID. For that I used the rule engine node. “If 1, return 1. If not 1, return 0”.
Before I could make the join, I had to group by ID and set the manual aggregation with the maximum of the column “orden”, which is the maximum number achieved by the counter. This step gave me something like this:
After that I added the Constant Value Column node to have a “1” attached to the columns to make some calculations. The pre-join:
And the final step:
I made the join by ID and the column “orden” making sure that the join happens only in the specific rows, which they did. After that, a bit of cleaning and it is ready.
Best regards!