I have several CSVs with dynamic width and I need to address the last, second last column of every csv to transpose them. That's how my file looks like:
Product | 2015-08-01 | 2015-09-01 | 2015-09-01 | 2015-10-01| Comment
ABC | 13 | 12 | 15 | 14 | Strong
CDE | 69 | 70 | 71 | 67 | Weak
FGH | 25 | 25 | 26 | 27 | Stable
With every new release of that excel file a date column is added and the comment adjusted for the last date:
Product | 2015-08-01 | 2015-09-01 | 2015-09-01 | 2015-10-01| 2015-11-01| Comment
ABC | 13 | 12 | 15 | 14 | 12 | Weak
CDE | 69 | 70 | 71 | 67 | 67 | Stable
FGH | 25 | 25 | 26 | 27 | 29 | Strong
For now I transpose two different sets, firstly I choose the comment and last date manually and secondly all other prices. Afterwards I join them and all is good. The problem is that I have a big set of files from the last years and I would need to set the last date dynamically to have a transposed table like this:
Product | Date | Comment | Price
With Product, Date and Price filled for every record and Comment just filled for the last reported date. Is there a way to do this?
related to this post: How to resolve duplicate column names in excel file with Alteryx?
It seems that what you want to do is to split that Comment column into it's on data stream?
After the transpose, you can crosstab into a form that contains the products as headers and the dates and comments as individual rows. Then a filter can pull the comment row out. A sort on the Name field would also let you grab the last row in that dataset to know which one was the last date.
For the crosstab:
- Grouping Fields: Name
- Header Field: Product
- Data Field: Value
Methodologies: Concatenate