How to count value occurrences in multiple columns

Hi,

I have data with customer names and whether they applied for the service of the bank, just like below. Columns will be marked in ‘Y’ if the customer applied for it.
|Name|Credit_Card|Chip_Card|Debit_Card|
|Andy|Y|N|Y|
|Judy|N|Y|N|
|John|Y|Y|Y|

I’m trying to count how many services did each customer apply for.
The following is the result table I want to get.
|Name|Credit_Card|Chip_Card|Debit_Card|Count|
|Andy|Y|N|Y|2|
|Judy|N|Y|N|1|
|John|Y|Y|Y|3|

Does anyone have any idea about this? Thank you very much!

Hi there,

you can use Column Aggregator node with Unique concatenate with count method on all services (columns) you want to count. After that use String Manipulation node to get number next to the Y letter with following expressions:
substr( $Count$ , indexOf ($Count$, “Y”) + 2 , 1 )

If customer can have 0 services you will get “(” as a final result in a row. Then you can use Rule Engine node to replace it with zero.

Give it a try and if you can’t make it I can share an example :wink:

Br,
Ivan

5 Likes

Hi Ivan,

Thank you very much! It works well in my case. Thank you.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.