Data360 Analyze

Hello, assume we have the input data as per below:

Image 1

Assume i want the input data represented as an output as below:

Image 2

Would this be possible to do for N amount of columns and N amount of data values, making it a generic solution without hardcoding each column name?

It would be nice to have this as a generic solution so i dont have to hardcode the column names i want to concantenate.

If a solution could be made, that is the same for 5,10,20 columns that gives me the output like in Image 2.

I can do this in a calculate fields node, with that said. I have to manually define each column i want to concantenate.

------------------------------
Henrik B
E.ON Sverige
------------------------------

I have been doing this a lot lately.  I use the Lookup node where the second input somehow specifies the rules for how the data in the first input should be combined.

So in this lookup, the fields in the second input are combined into  an output field.  the name of that output field is specified by the variable named outputFieldName which in this code is hardcoded.  You could of course calculate the name from the fields in the second input like you seem to have done, but dynamic field names might be problematic for other nodes to deal with like you described.

In this example, the "Get Metadata", "Pivot Data to Names", and "Head" nodes are just executing some logic that identifies the columns that need to be combined dynamically.  You would replace these with whatever you actually need.

In the Lookup node, I change ConfigureFields to Advanced so that I can take better control of the output field definitions and save the names of the fields that will be combined.

ConfigureFields:

outputFieldName = "CombinedData"
outputs[0][outputFieldName] = unicode

myFieldList = []
for name in inputs[0]:
    if name in inputs[1]:
        myFieldList.append(name)
    else:
        outputs[0] += inputs[0][name]

The ProcessRecords code will do the work of combining the data together using the field list that was calculated in the ConfigureFields code.  Make sure to be prepared for Nulls in the input data.  And this uses the unicode so that any of the datatypes get converted to a corresponding string representation.

ProcessRecords:

outputs[0] += inputs[0]
outputs[0] += inputs[1]

data = []
for name in myFieldList:
    value = inputs[0][name]
    if value is Null:
        data.append("")
    else:
        data.append(unicode(value))
outputs[0][outputFieldName] = ",".join(data)

The output looks like this:

------------------------------
Ernest Jones
Precisely Software Inc.
PEARL RIVER NY
------------------------------

While this solution is not as flexible as that provided by Ernest you can perform the concatenation in a Transform node. I have provided the logic to name the output field to be the concatenation of the input field names but I do agree with Ernest that this may cause some issues with certain nodes or external systems. 

------------------------------
Adrian Williams
Precisely Software Inc.
------------------------------