Split to columns based on calculation

[znooredeen004]

I would like dynamically create columns based on value:

My Data looks like this:

Product Amount	Product Life in Months	Amount per Month to be amortized
100	10	10
50	5	10

My goal is have a tool create columns based on the product life per month and then populate the amount per month

For example, the first row would produce 10 additional columns with the amount 10 in those 10 columns. The second row would produce 5 additional columns with 10 in those 5 columns.

Would this be possible in alteryx?

[rzdodson]

@znooredeen004 if I understand your requirements correctly, what you are looking for is the Generate Rows tool. If the number of rows is fairly static (i.e. it is based on the Product Life in Months), then you can actually use that as a field when populating the number of rows you are needing. Otherwise, if this ever becomes a different number than the 10 months per product you are looking for, simply sub out that variable in the Generate Rows for the static value you are needing.

EDIT: I see your requirements to have new columns populated vice new rows. Will have a solution mocked up for you soon.

[rzdodson]

@znooredeen004: here is an iterative macro solution that should get you pretty close.

In the initial workflow level, you'll need to assign a RecordID field (mine is called "Iter") that will help support an iterative macro. From there, I build the iterative macro to ingest the Product Life in Months field as the main variable in creating the number of rows, reoriented the data so that the number of months are represented in their own columns, then created a dynamic file path that saves the outputs in the same directory where the workflow is saved.

Initial workflow

Macro layer

[znooredeen004]

Is there any way you can have the workflow compatible with Version 2020.4.5.12471? I'm on a work laptop and am not able to update at the moment

[aatalai · Accepted solution]

@znooredeen004 try @rzdodson method to generate rows and then use the crosstab try with one row