merging nearly identical rows

[ald]

Hi Folks

I am needing some support again after a long time away from Trifacta Cloud. Lots have changed. Help is gratefully appreciated!

Thank you upfront!

I have the following situation:

PICTURE A

I would like to merge these two rows into one by achieving the following:

(essentially filling the missing information from the other row either above or below that has it)

PICTURE B

Before finally removing the duplicate rows to achieve the following:

PICTURE C

[AmitMiller]

Hello friend =]

What you'd want to use is the FILL() function.

The fill function fills any missing or null values in the specified column with the most recent non-blank value, as determined by the specified window of rows before and after the blank value.

So in your scenario, you can edit with formula all 4 columns in question (all those that require filling) and use the fill function as follows:

Fill($col,1,1)

The $col variable is a reference to the column(s) that are currently being evaluated.

While the '1' in each following parameter refers to the "size" of the window - how many records to look up and down to fill the desired missing values.

You'll also need to use 

1. group by field to make sure you only fill fields with the proper context (i.e., group by GW_Work)
2. sort by field; this is a necessity as all window functions need to be sorted.

note - if you want to fill by looking as many rows up or down without limitation, you can use '-1' for that.

You can see the full documentation of the function here

Hope this helps; please feel free to ask any additional questions! =]