This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
@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"?
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.
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.