Best Of

@renee12345 You can use a Group by tile on the data that includes Name to find the First Non-Null value for Name by ID. Then join that to the data without name on ID to fill in the Name column before appending.

@NateBI I was able to accomplish what you are looking to do using a Redshift Dataflow. If you don't currently have Redshift dataflows enabled you can contact your CSM, or you could attempt this same logic in Magic.

First, I created a new field that gives the order of the data, similar to rank but for every row in sequential order.

Second, I used this case statement in the Dataflow:

CASE

WHEN "rank" = 1 AND lead("rank", 7) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" = 2 AND lead("rank", 6) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" = 3 AND lead("rank", 5) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" = 4 AND lead("rank", 4) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" = 5 AND lead("rank", 3) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" = 6 AND lead("rank", 2) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" = 7 AND lead("rank", 1) over (partition by "rule2_hours" order by "order") = 8 THEN 'Colour'

WHEN "rank" >= 8 THEN 'Colour' ELSE 'No Colour'

END as "testing"

And it worked! See results below:

This was fun, thanks for the challenge. If this solved your problem please accept this as the answer, if not let me know and I'll attempt the same thing in Magic ETL.

First, we should solve problem #2. Then, we'll create a canonical lookup table from Number to City to solve problem #1.

Use an Add Formula tile to make two new columns, Canonical Number and Canonical City like so:

Canonical Number formula: NULLIF(STR_DIGITS(CASE WHEN City BETWEEN '0' AND '999999' THEN City ELSE Number END), '')
Canonical City formula: NULLIF(STR_REMOVE_DIGITS(CASE WHEN Number BETWEEN 'A' AND 'zzzzzz' THEN Number ELSE City END), '')

Now use a Group By tile to make the lookup table. Our one grouping column will be Canonical Number, and our one aggregate will be the First non-null value of Canonical City; we'll call this the Lookup City.

Now use the Join Data tile to Inner Join the original table (the output of the Add Formula) to the lookup table (the output of the Group By) on the Canonical Number column.

The Canonical Number column and the Lookup City column in the result of the Join Data tile have the final values you want.

The whole transform has this shape: