Filling Blank Cells with Adjacent Data

[teamOC]

Any thoughts on how to prep my data to go from

Level 1	Level 2	Level 3	Level 4
[null]	[null]	Steve	Rob
Steve	Mike	John	Bert
[null]	Steve	Rob	Jim
[null]	[null]	Steve	Al
[null]	[null]	[null]	Steve

to

Level 1	Level 2	Level 3	Level 4
Steve	Rob	[null]	[null]
Steve	Mike	John	Bert
Steve	Rob	Jim	[null]
Steve	Al	[null]	[null]
Steve	[null]	[null]	[null]

My thoughts maybe a formula to see how many cells are null left of Steve and then move all the names in the row over by that number?  The logic makes sense in my mind but no idea where to start with Alteryx.  Appreciate any thoughts and insights!

[cpet13 · Accepted solution]

@teamOC I think this solution gives you what you want. In essence, you combine all the fields into one, remove the null values, then split the combined column into multiple.

[teamOC]

This is genius, putting it all in one field and cleaning it that way.  Thanks!

[JBLove]

@teamOC 

Here is one method, that will allow the values to be re-positioned across the levels:

1. Add a RecordID tool to sequentially ID your rows.  (This is unnecessary if you already have a unique ID in your data set)

2. Using a Transpose Tool, and select RecordID as your "Key" and select your Level1 thru Level4 columns in the Data Section.

3. Apply a filter on the Value column to remove your null values.  This is what I used: !IsNull([Value])

4. Use  a Multi Row Formula tool to create a SEQ ID.  I used the "Create New Field option" creating a field called "SEQ".  Make sure to select "RecordID" in the Group by prompt and use the following formula:  IF IsEmpty([Row-1:SEQ]) THEN 1
ELSE [Row-1:SEQ] +1 ENDIF

5. Next use a formula tool to generate your new Levels.  I used this expression:  "LEVEL"+ToString([SEQ]) to generate a field called "NewLevel".

6. And last, use the Cross Tab tool to re-arrange the data in the desired arrangement.

    Group Data by RecordID,  Use "NewLevel" as your column headers and "Value" for the Values for New Columns.

[cpet13]

You are welcome! I updated the file I had attached, there was a minor error that was causing something funky to happen with one of the rows.

[shancmiralles]

hi !
i got a different solution for this ... i use the RECORD ID  - TRANSPOSE (key column : record#  || data columns: Level 1 - 4 ) from here i connected 5 FILTER tool ( column: record # | = | record # for each filter).

for each filter 1 connected 2 branched: frist branch starts with a SORT tool ( Value: descending)  then connected a SELECT tool to deselect field "Name".  2nd branch is another select tool deselecting field "Value". both branch are combined using the  JOIN MULTIPLE tool ( choose Join by Record Position) uncheck/deselect the Input#2 field "record#"..

after doing this for all 5 record numbers ( i just created 1 set and copy paste it and changed filter value from 1-5 for each) i combined them all using the UNION tool  then finally CROSS TAB tool ( group data : record# | change column headers: name | value for new columns: Value | Method: Frist)

i know this is long.. but it was fun to do.. thanks!