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

[ctackett]

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. 

DateHour	ForecastDate	NorthWestZone
2023-09-24 01:00	2023-09-24	628
2023-09-24 02:00	2023-09-24	815
2023-09-24 03:00	2023-09-24	725
2023-09-24 04:00	2023-09-24	610
2023-09-24 05:00	2023-09-24	333
2023-09-24 06:00	2023-09-24	135
2023-09-24 07:00	2023-09-24	993
2023-09-24 08:00	2023-09-24	659
2023-09-24 09:00	2023-09-24	444
2023-09-24 10:00	2023-09-24	134
2023-09-24 11:00	2023-09-24	773
2023-09-24 12:00	2023-09-24	689
2023-09-24 13:00	2023-09-24	795
2023-09-24 14:00	2023-09-24	481
2023-09-24 15:00	2023-09-24	954
2023-09-24 16:00	2023-09-24	177

[Pang_Hee_Choy]

if I understand correctly, you have 2 csv file daily and want to split them by zone. 

1. rename the csv file by zone name. example, North West forecast.csv and South East forecast.csv

2. load the csv with filename, group them by crosstab. 

[FinnCharlton]

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!

[ctackett]

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

Thank you!

[ctackett]

@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.