If this were Excel I'd use = COUNT(one row)

[rinaldiinjapan]

I’ve googled/searched this to death so my apologies if this already been answered as I’ve seen at least half a dozen similar questions but none helped me. Here is a sample dataset.

My apologies for the formatting! I saw this and added an attachment to better show it.

                    Column1      Column2     Column3

Row 1…Missing …3…9
Row2 …0…?Help? …9
Row3…1…9…0

So what I need to know is which rows have any cells containing text with a new column. One way to do this in Excel is COUNT(Column1 through Column3). I would hope to see the following:

                    Column1      Column2     Column3    NEW COLUMN

Row 1 Missing 3 9 2
Row2 0 ?Help? 9 2
Row3 1 9 0 3

With this new column I can now further process this data as needed. So in a nutshell, how would you count the number of cells in a row to differentiate to know which ones are text vs numbers?

Thanks in advance, I’ve seen many responses recommending Rule Engine, etc but it’s not clear how to achieve these results. Thanks again. Love this community.
m
Sample.1.xlsx (10.5 KB)

[ArjenEX]

Hi @rinaldiinjapan

You can approach this in quite a few different ways. Below is one:

First cleanse your columns to only keep numbers and make everything else null. You can use a multi column String Manipulator for this. toNull(regexReplace($$CURRENTCOLUMN$$,"[^0-9]",""))

Then merge all columns into a collection by using the associated node for this. Make sure the checkbox to ignore missing files is selected.

Then in a Column Expression, count the number of elements in the array that was just created. Function:
arrayLength(column("AggregatedValues"))

Hope this provides some inspiration!

[rinaldiinjapan]

Thank you ARJEN! As excited as I was to finally get a to use the String Manipulation node it kept giving me an error but I probably was mis-using it as it would result in all columns being blank but nevertheless with the additional use of Create Collections Columns and Column Expressions (both of which I’ve never heard of) I was able to get what I needed. Definitely inspired and thank you so much for the guidance.
m

[takbb]

Hi @rinaldiinjapan, an alternative would be to follow @ArjenEX’s String Manipulation (Multi Column) with the Column Aggregator node.

Since all non-numeric columns are now missing, you can count the columns excluding missing values like this:

However, String Manipulation (Multi Column) does have problems if some of your columns are not of data types it can handle (e.g. Long, or Boolean) which could the problem if you are seeing an odd (and fairly meaningless) error message. If that is the case, you need to split out those columns prior to the String Manipulation (multi), and then bring them back together again using column appender prior to the subsequent “counting”, which is a bit of a pain , or else exclude them in the config on the String Manipulation (Multi) node.