How to split one column of data into several records having multiple variables.

[SBerube]

Hi,

I'll start by saying that this is my first post, and I hope I'll do a good job at explaining my problem precisely. Also, I did looked in the community, but didn't find exactly what I am looking for.

I'm extracting data from a website, have I'm having problems processing it to create a data table. So far I managed to extract one column which contains all my data (hundreds of rows). Each data record has a data followed by 11 variables. So row 1 =  record 1 (or date 1), row 2 = record 1 and var 1... row 13 = record 2 (or date 2), row 14 = record 2 var 2, etc.

At the end I'd like to have the field "date" and the 11 variables in columns and as many records as I have rows. Attached is an Excel file which replicates the data structure, but you'll understand that in the real scenario data comes from a "download" web connector.

Thank you fro your help.

Thank you

[nick_ceneviva · Accepted solution]

This can be accomplished using a combination of the multi-row formula tool and the Cross Tab tool.  The attached example should get the data into your desired output.  You could then change the column names to match what you are looking using a text input tool and they dynamic rename tool or using the select tool.

Hope this helps and let me know if you have any questions about the solution!

[Kenda]

Hey @SBerube! Try the attached workflow where I used your sample Excel workbook. Note the workflow assumes that the amount of rows that belong to each date is fixed. Also, you will have to label the new columns, potentially with a Select tool after the Cross Tab.

[SBerube]

Thank you very much Nick,

This is very simple and it works great!

[SBerube]

Thank you very much; your solution works too! :)

[anaaz]

I have old version alteryx how can i open this file to look at the  formula .... its poping up a message this document is old cannot open

[anaaz]

my old version is not supporting to this file....  is there any way to file this file for the answer?

[LordNeilLord]

Hey @anaaz

If you open the yxmd file with a text editor like notepad... On the very first line you will notice the version the workflow was created with... Change this to your version and save

Neil

[anaaz]

how can I open it with text editor like notepad?

[Kenda]

Right click on the .yxmd file after you have downloaded it to your computer.