Trying to capture a header and making it a value in a column

Question

Evening Folks,

I've been working on data all day and this one I've got to work, but when we started building Tableau reports the format we used was a bad idea so I'm trying to restructure this using Alteryx.

I'm trying to make two outputs from this 1 CSV file.

The first 24 hours are readings

The second 24 hours are tickets

I'm trying to take the header from the column directly to the right of "Yesterday Actual" and make that into a column called "Forecast Date" which each cell has that date in it.

The End Result looks like this

I was going to transpose the columns and make the date the rows and then combine it with the Hour so it's in a format that Tableau works well with such as yyyy-mm-dd hh:mm

I was trying to find a way to have a header named NorthWestZone - I thought about using Formula Tool but that makes the value NorthWestZone and I'm trying to make the header that and the values in the .csv file are coming from that Zone.

I have two forecast.csv files for two zones and my goal is to have one column with the NorthWestZone values and then another column with the SouthEastZones ... the .csv files I receive every day and I run Atleryx Schedule to pull them in, but when I try to create reports with it, the structure is just not working.

Once I have it in this format, then I'm going to output the data to a database table and then use that to connect to Tableau to build reports.

Any guidance would be great, I've spent a good 3 days trying to flip this data about.

DateHourForecastDateNorthWestZone2023-09-24 01:002023-09-246282023-09-24 02:002023-09-248152023-09-24 03:002023-09-247252023-09-24 04:002023-09-246102023-09-24 05:002023-09-243332023-09-24 06:002023-09-241352023-09-24 07:002023-09-249932023-09-24 08:002023-09-246592023-09-24 09:002023-09-244442023-09-24 10:002023-09-241342023-09-24 11:002023-09-247732023-09-24 12:002023-09-246892023-09-24 13:002023-09-247952023-09-24 14:002023-09-244812023-09-24 15:002023-09-249542023-09-24 16:002023-09-24177

forecast.csv

ctackett · Answer

@FinnCharlton  this looks great but I get one error that I'm not sure why, when I use the original data. It's structured exactly the same, but when I open the original I'm noticing spacing in the .csv and in the test file I attached earlier that spacing is gone. So I'm wondering if that's the issue, but everything else is exactly the same.

screenshotdata.png

ctackett · Answer

See, I was trying to figure out how to make the Rename tool work, will check this out,

Thank you!

FinnCharlton · Answer

Hi @ctackett , how does this look?

First we use the Select Records tool to just take one of the tables.

The Dynamic Rename tool renames our fields to the correct names.

We can then transpose the date columns into rows.

The formula tool creates the DateHour field by parsing the dates adding the hours to those dates.

We can then rename the fields with the select tool.

Hppe this helps!

Parse Tables.yxmd