Parse and auto arrange data coming from Excel

[patoliadv]

@NicoleJohnson I am struggling with parsing the data coming in excel . can someone please assist me with this?

I have a description column that i want to parse into 11 columns. but the order of data in each row will be different.

Description
2019 | AA Ltd | London | Completed | Factory | Jose | Phones | 4/3/2019 | 4/15/2019 | 4/15/2019 | 4
CC LTD | India | Pending | Industry | Sarah | Earphone | 3/5/2019 | 3/14/2019 | 3/14/2019 | 10.00
BB Ltd | 2019 | UK | Completed | Factory | Joseph | 4/3/2019 | 4/15/2019 | 4 |

Below is what i want the output to be in 3 rows.

Year   Company    Location      Status         Building     Name      Devices         Receipt Date       Start Date        Delivered date Hours
2019   AA Ltd          London        Completed   Factory       Jose        Phones         4/3/2019              4/15/2019         4/15/2019                4
null     CC Ltd          India            Pending        Industry     Sarah       Earphone     3/5/2019              3/14/2019         3/14/2019                10.00
2019   BB Ltd          UK               Completed   Factory      Joseph      NULL           4/3/2019              4/15/2019         null                           4

I can use text to column tool and split the data based on | pipe delimiter. But how to order those to tell it to go to year if its "2019" or Name if its "Jose"?

[NicoleJohnson · Accepted solution]

Hey! Per our conversation on messages, please find attached possible solution!

Cheers,

NJ

[patoliadv]

@NicoleJohnson Thanks a lot. You are super fast. this will definitely help me get started.

[patoliadv]

@NicoleJohnson How to handle a situation where the dates are not in order i mean for the below scenario.

For the 1st example first date is null and for the 2nd the middle date is null.

2019 | AA Ltd | London | Completed | Factory | Jose | Phones |  | 4/15/2019 | 4/15/2019 | 4
2019 | AC  Ltd | London | Completed | Factory | Jose | Phones | 4/15/2019 |  |4/15/2019 | 4

[NicoleJohnson]

You will need to determine some sort of logic for deciding which dates are which - there is no way for Alteryx to just "guess" which dates should fall into which column... however, if your data always ends with hours field and then the last three columns are always going to be your date fields, you could try using a process where you take the last 4 fields for each Record ID (try the Sample tool) and then you can rename them to Received, Started, Delivered, and Hours. 

The rest of the logic in the sample workflow I provided would then be able to find your year and then the rest of the fields. 

This process will likely have to go through a few iterations before you find the right combination 🙂 If you can locate some sort of "key" that tells you which field is which based on some identifying characteristic from each record (for example, if the company is AA Ltd, then the order is 123, but if it's AC Ltd, then order is 213... etc.) then you might be able to apply some different logic to tell Alteryx what the pattern is based on that mapping. If that is an accurate statement, let us know and we could help you come up with an example that would work for that type of scenario as well.

NJ

[patoliadv]

Thanks Nicole , @NicoleJohnson so the last 4 columns will always be 3 dates followed by hours, however they can be null like below.

4/3/2019 | 4/15/2019| 5/14/2019 |  |

  | 3/14/2019 | 3/14/2019 | 10.00

4/3/2019 | | 4/15/2019 | 4

There are few fixed values for the 2 columns like Status can only be completed or pending with an exception that it can have null.

Building can only have factory or industry or null.

location is fixed too , it will never have a null or empty value but it can repeat between rows.

How to order based on a key column? do you have an example? 

[NicoleJohnson]

See attached for a modified example. By ordering the fields initially and then taking the last 4 (using Sample tool), you can rename those fields automatically, since they'll always be in the same order. Nulls will be included as part of your Split to Rows step, so if there is a null date in those last 4 fields, it will process correctly.

Then you can skip those 4 fields (since you're already handling the dates & hours fields separately) and use the remaining logic from my original example to get your columns ordered correctly, and Join Multiple everything back together at the end by RecordID.

NJ

[patoliadv]

@NicoleJohnson Thanks a lot, i will use this to make it work for my scenario.