How to Auto-fill blank cells for Sales Location.

[Henry_Gunn]

Hello,

Super new to Alteryx, so I apologize if this is a stupid question:

In my day-to-day job I have to organize/manipulate data that comes in the same excel template, just a different number of rows each day. Its sales data. The salespeople are technically supposed to fill in EVERY cell, but often do not. The most frustrating is the location data. There are 3 columns, Origin/Destination/Location. They should all hypothetically be the same, but often are left blank. The problem is that they are all left blank to different degrees, depending on the salesperson data entry style. Here is a sample of what I'm talking about:

Using Excel, not a heavy lift, I just made an additional column I call "Location 1" to just auto fill the data that's located in any/all of the 3 cells. =INDEX(RC[-3]:RC[-1],MATCH(TRUE,INDEX((RC[-3]:RC[-1]<>0),0),0))

Is there a way to do this using Alteryx? I'm very interested in trying to automate the system as much as possible.

Please let me know what you think and thank you.

[DataNath · Accepted solution]

Does something like this look alright @Henry_Gunn?

The workflow:

1 - Creates a temporary RecordID so the correct [Location1] can be joined back on later

2 - Transposes the data so that all values for each record are brought into the same column

3 - Summarizes by the Max value (location) for each RecordID - max here finds the longest string so basically the biggest non-null for this scenario

4 - Joins this Max value back onto the main dataset. As part of the join, the temporary fields are removed and the new field renamed to Location1 as per request

[Henry_Gunn]

Thank you so much, that seems to work perfectly. 

I really appreciate it, I wasn't sure anyone would ever respond and certainly not so quickly.

Very much appreciated.