Search code examples
google-cloud-platformgoogle-cloud-dataprepwrangle

In DataPrep, sum a set of many columns or values in an object

I have a DataPrep dataset which contains a series of ~10 columns, each of which indicates whether or not a particular brochure was selected:

BRO_AF    BRO_SAF    BRO_SE   ...
1                    1
1         1
                     1

I'd like to sum/count these values into a BrochuresSelected column.

I was hoping to use ADD with a column range (ie BRO_AF~BRO_ITA), but ADD only takes two numbers.

I can't use COUNT, as it counts rows not columns.

I can use NEST to create a column storing a map or array of brochures, but there doesn't seem to be a function for adding these. I can't use ARRAYLEN on this column, as even empty columns are represented in the column (eg ["1","","","","",""] would have an array length of six, not one).

Has anyone solved a similar issue?

Solution

  • If you know the column names, you can use the + operator in a derive transform. For example:

    enter image description here