Change table format of column value to field name

Question

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 IDDate# SalesDiscount15/22/20235550%25/22/20236520%15/23/20237330%25/23/20233515%15/24/20234425%25/24/2023896%15/25/2023658%25/25/20234220%15/26/20232330%25/26/20231250%

Expected output:

Record IDDate# SalesDiscountRecord IDDate# SalesDiscountRecord IDDate# SalesDiscount15/22/20235550%15/23/20237330%15/24/20234425%25/22/20236520%25/23/20233515%25/24/2023896%

FrederikE · Accepted Answer

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 Answer

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