Reformatting data from Single Row to Multiple Rows with other formatting

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

ExampleDataAndOutput.xlsx

Workflow

Accepted answers

caltang

Here ya go!

For TonySco.yxzp

All comments

caltang

Here ya go!

For TonySco.yxzp

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

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703