Hi - I am trying to get a bunch of different rows into 1 row of data. For example, we might have 4 lines all from the same user, but we need the data from all of the rows combined into 1. I've uploaded a photo of what I am explaining.

[4b788ccc17e40055cbd9]

The top photo is an example of what the data currently looks like, and the bottom part is what I would like the data to look like. How can I accomplish this?

Note: this data was manually created to show an example, but the unique key between the individuals would be 'User'.

[Trifacta_Alumni · Accepted solution]

Hi @CS User?,

Thanks for providing the screenshots! In Trifacta, you can use the "Group By" transformation with either the LIST or ANY function to collapse multiple records into a single record using a unique key.

Here's how you can apply this transformation to your example data:

1. In your example, you will want to start by replacing the "norecord" values with null values. This ensures that the "norecord" values won't overwrite the "COMPLETE" values in your final dataset. You can use the "Edit with formula" transformation to replace the "norecord" values in each of your data columns, as shown in the screenshot below:

The full step to create is shown below:

set col: D1,D2,D3,D4 value: if($col == 'norecord', null(), $col)

2. Next, find the "Group by" icon in the transformation toolbar and choose "Group by" from the drop-down list of options.

The "Group by" builder will open on the right side of your screen. Enter your unique key columns in the "Group by" field (these are Date, Time, and User in your example).

In the "Values" field, you can use either the ANY function or the LIST function. The ANY function will return any non-null value for each group, and the LIST function will return an array of all non-null values for each group. You can see the different outputs in the two screenshots below:

Based on your desired output, ANY will likely be the better approach.

Choose "Group by as new table" from the "Type" dropdown to finish the transformation. Here is the full text of the transformation:

groupby group: Date,Time,User value: any(D1),any(D2),any(D3),any(D4) type: agg

3. To finish, you can clean up the column names by using the "Rename columns" transformation with the "Find and replace" option selected. This will allow you to remove the "any_" string appended to each of your new columns. Here's how this step looks:

You can also insert the "norecord" value back into each column by using the "Edit column with formula" transformation combined with the IFMISSING function, as shown below:

The full step for this operation is below:

set col: D1,D2,D3,D4 value: ifmissing($col, 'norecord')

That's it! If this helped, please mark the answer as "Best" so that other users know your question has been answered.

[4b788ccc17e40055cbd9]

Awesome thanks!