I’m trying to use a GroupBy node to count the occurrence of a value in a column. Eg, table like this (from a cross joiner):
col1 col2 value
A A 0
A B 1
A C 2
B A 2
B B 0
B C 0
and so on in a big table,
I want to group by the first column and count how many of a certain value there are, eg for 0 output
A 1
B 2
I think the GroupBy node is what I need, but I’m not sure?
To answer my own question, I found a way but it doesn’t seem elegant.
I can filter by the value I want to count, then GroupBy the main column using the count function.
At the moment I don’t need the other rows, but if I need them later I can always bring them back with a concatenate.
Hello @phick
For your convenience… just replicate the target column and group over it.
Rule Engine: $column1$ LIKE $column1$ => $column1$
BR
I’m not sure I understand how this helps?
To give more detail, I am generating a large cross table from two data sets (call them A and B), then calculating a score based on various column values (this is currently an integer between 0-5). What I am working it out is how many times an entry in the input table scores 0, so the output would be the same number of rows as table A, with a column value varying from 0 up to the number of rows in column B.
Hi phick,
the result you are describing can be achieved by using ‘col1’ and ‘value’ as the columns to group!
Best
E
You only need to develop a little more… I am just delivering your desired output
group by the first column and count how many of a certain value there are, eg for 0 output
A 1
B 2
At the moment I don’t need the other rows, but if I need them later…
From my point of view the key is the Rule Engine node. the rest is displaying arrangement
BR
That’s perfect, exactly what I was trying to do. I group by my first table (ie col1) and the score value, then count how many of the second table (col2) get each score.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
