Any thoughts on how to prep my data to go from Level 1Level 2Level 3Level 4[null][null]SteveRobSteveMikeJohnBert[null]SteveRobJim[null][null]SteveAl[null][null][null]Steve to Level 1Level 2Level 3Level 4SteveRob[null][null]SteveMikeJohnBertSteveRobJim[null]SteveAl[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!

Filling Blank Cells with Adjacent Data

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

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!

Preparation

Accepted answers

cpet13

@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.

Example.yxmd

All comments

cpet13

@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.

Example.yxmd

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!

jacky324

Hi, do you mind sharing your yxmd file as an example? Thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703