Change table format of column value to field name

[cfvito]

Not sure if this is possible I have data that I want to change the table from vertical to horizontal based on Date.

Original Data

Record ID	Date	# Sales	Discount
1	5/22/2023	55	50%
2	5/22/2023	65	20%
1	5/23/2023	73	30%
2	5/23/2023	35	15%
1	5/24/2023	44	25%
2	5/24/2023	89	6%
1	5/25/2023	65	8%
2	5/25/2023	42	20%
1	5/26/2023	23	30%
2	5/26/2023	12	50%

Expected output:

Record ID	Date	# Sales	Discount	Record ID	Date	# Sales	Discount	Record ID	Date	# Sales	Discount
1	5/22/2023	55	50%	1	5/23/2023	73	30%	1	5/24/2023	44	25%
2	5/22/2023	65	20%	2	5/23/2023	35	15%	2	5/24/2023	89	6%

[FrederikE · Accepted solution]

Hey @cfvito,

This is a (not very dynamic) way to do this. While it is possible to transform data this way, this is certainly not recommended as Alteryx (and other data tools) doesn't work very well with such data.

[ArnaldoSandoval · Accepted solution]

Hi @cfvito 

I liked your problem from an educational point of view, as it could probably be handle with macros, actually an interactive macro based on this tutorial Create an iterative macro; I am glad that @FrederikE posted a solution implementing Crostab-Transpose tools in his solution, which should be the way to handle your case. Nevertheless I liked the idea for implementing an interactive macro, before continueing, I agree with Frederik the data is transformed into an structure hard to work with, anyhow, you should have reasons for that.

Back to my approach, first I implemented a low key solution, to understand how to handle the transformation, this solution showed me that as we keep adding columns to the right, we also expend a great deal of time renaming and disabling columns names, below is its workflow; it created two fields Date_Group and GroupID, the GroupID is used to append sets of columns to the right.

The solution above gave the ideas to implement an Iterative macro, as GroupID move from 1 to 5; While implementing the macro I found how complicated is to debug macros, so I included debugging ideas into the macro I was developing! The second solution workflow feature a "Create Loop-0.yxdb (One Off)" container, this is for debuggind the macro, the purple area was replaced with the call to the macro.

Basically, it is similar to the Solution 1 WF, just feeding data to the macro.

The challenge was writting the macro "Append_Set_Columns" (perhaps not the best name); the macro is using the variable "Group Number" to loop 5 times over the source data, the red line on its workflow below illustrates the loop

The "Debug Output 1 yxdb file per loop" is a debugging concept trailed on this macro, you can disable the container to avoid the L-#.yxdb generation, each one of these L-# files contains all the variables in the data stream per iteration.

The macro does its required job, although, it generates lots of columns, that were excluded from the results, and some columns rename apply by the parent workflow right after the macro exit its loop.

Hope this helps,

Arnaldo

[cfvito]

When you mean it is not recommended, is it because the numbers or data could get mixed up if I add in a large amount of data in the future?

I followd your workflow, it helps alot. It is too much work to set it up though but this is exactly the output I was aiming for. I can't believe I have never thought of this option haha. Thanks alot!

[FrederikE]

Hey @cfvito,

No, the data should be fine. It's more about performance and how it can be handled. Alteryx performs excellently with millions of rows, but it gets its problems with hundreds of columns. This also applies to relational databases, Tableau, and many other data tools. Also, any further transformations get much more complicated. 

It should be fine if your output is just for human readability in Excel or something similar (and the dataset is not to huge).