Reformatting data from Single Row to Multiple Rows with other formatting

[TonySco]

Howdy Alteryx community,

I am trying to prepare data to upload into a system of record. I currently have 5 columns of data and I am trying to reformat it in such a way that 1 row will become up to 5 rows. 

I have attached an excel file with 4 rows of example data in one sheet and the desired output format in the other sheet. In the output file, the first column and fourth column are entirely constants.

I could handle most of the re-format on my own, but for some reason moving the data from columns 2-5 into rows has been throwing me for a loop. Any help would be much appreciated, please feel free to reach out with any clarifying questions if needed.

Thanks,

Tony

[caltang · Accepted solution]

Here ya go!

[TonySco]

Thank you very much for this timely response caltang!

When using the original data, however, the "Value" column seems to be out of order. I'm not sure what exactly is happening, but it looks like sometimes the first 1 or 2 columns of text data are placed above the H row that separates the records and then there are blanks in where I would expect them to be below the H row. For example, the first two rows are I rows that correspond to the first record (which has 2 total records), before the H row. Below the H row, there are 2 blank rows and then a line which corresponds to the 3rd row of the second record. Some records that I would expect to have 2-3 rows are only returning 1, but I think the data is spilling into other records.

I'm not sure entirely what is occuring, but I can provide an example that shows you what I am looking at if needed. If it makes it easier, it would work for me if there were always 5 rows (1 H row and 4 I rows with blank rows when there is not data).

Thanks for any assistance!

[caltang]

Goodness that is a very long wall of text and I appreciate the fine details & granularity of your issue - but it’s much easier to see a screen capture of the problem. 

A picture paints a thousand words my friend @TonySco ! 

But based on what you explained, it’s not gonna be a hard fix. But I still need to see some data/pics of the problem.

p.s, I’m going to bed soon. I’ll most likely have a look tomorrow morning once I go on my commute. 

[TonySco]

Of course. I have added an ExampleIssue sheet, where I duplicated the issue that I was seeing in the first 5 records. The data set is around 3500 records. If it makes it easier, it would be fine if it was always 5 rows per record, with blanks or a uniform placeholder where there is not data in the original set.

[caltang]

I’m on my phone right now but from the looks of it, this is a sort issue.

 

The Sort Tool is only sorting by record ID. You can play around having multiple fields sorted - focus on tile_num and tile_sequence_num

Now, I also see some blanks in your workflow, I’m not sure if you configured further by adding a data cleanse tool or not - but I think the blanks will cause some issues since I only accounted for nulls in the original dataset. 

If you don’t mind, can you provide some data that have those blanks (white space) instead of pure nulls?

[TonySco]

You were right! It was a quick fix once I started tinkering with the sort. I was able to get what I was looking for by sorting by tile_sequencenum ascending. Thank you so much for your help and I hope you had a great night's sleep!

[caltang]

Glad it helped!