Hi!
I am trying to count by value, but only the first time they appear. I.e:
| Value | Sequentual Occurence Value |
|---|---|
| A | 1 |
| C | 1 |
| B | 1 |
| A | 0 |
| A | 0 |
| C | 0 |
| B | 0 |
| D | 1 |
| D | 0 |
I managed to make a count by value and date that gives me the sequential order but I also need this one to be able to count the number of “real” cases.
Best regards!
Hi!
I have it. Since I had the order in wich the data appears, I only had to add a Rule Engine node and keep only the “1”. I was stuck but It was really easier than I wanted it to be 
Best regards!
But what if I want to have, for example, the 1 in the last case? Something like this:
| Value | Sequentual Occurence Value |
|---|---|
| C | 0 |
| B | 0 |
| A | 0 |
| A | 1 |
| C | 1 |
| B | 1 |
| D | 0 |
| D | 1 |
A simple solution to your second question would be to first sort the rows in their inverse order, then use your solution and eventually sort the rows back as initially.
Hope this helps.
Best
Ael
Hi!
What I ended up doing was this:
I groupby value and use the original order value to get the maximum value, attach the constant value 1 and then join by maximum value and value.
I do not know if it is clear. If someone has any question I can explain the solution in detail.
Thank you though, @aworker.
Best regards!
Sure a little more detail would certainly be helpful
br
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!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
