How to build a for loop which is dynamically changing column values for each row?

Hi everyone!

I have below code which is written in SAS.

It is basically a loop which is looping from start_curve column to end_curve column. cal_day column is being incremented every time.

I have 366 columns named as days_calls1, day_calls2,...., day_calls_366.

I have 90 columns named as dayPct1, dayPct2, .......day_pct90.

How would I be able to achieve the same functionality in Alteryx?

Any ideas would be appreciated.

Input data would be as below:

cal_day is 1 in the input.

start_curve	cal_day	end_curve	day_calls1	day_calls2	day_calls3	day_calls4	day_calls5	TotalCalls	dayPct1	dayPct2	dayPct3	dayPct4	dayPct5
3	1	5	0	0	0	0	0	10	1	3	5	7	9
3	1	5	0	0	0	0	0	20	1	3	5	7	9
3	1	5	0	0	0	0	0	30	1	3	5	7	9
1	1	4	0	0	0	0	0	40	2	4	6	8	10
1	1	4	0	0	0	0	0	50	2	4	6	8	10

Output would look like this:

You can ignore cal_day of the output. It is being updated after every loop and more or less basically means how many times loop ran.

Values from 1st to 3rd row for days_calls is calculated using dayPct3, dayPct4, dayPct5 as start_curve is 3 and end_curve is 5. (e.g. day_calls1 = totalCalls * dayPct3)

Values from 4th to 5th row for days_calls is calculated using dayPct1, dayPct2, dayPct3, dayPc4 as start_curve is 1 and end_curve is 4. (e.g. day_calls1 = totalCalls * dayPct1)

start_curve	cal_day	end_curve	day_calls1	day_calls2	day_calls3	day_calls4	day_calls5	TotalCalls	dayPct1	dayPct2	dayPct3	dayPct4	dayPct5
3	4	5	50	70	90	0	0	10	1	3	5	7	9
3	4	5	100	140	180	0	0	20	1	3	5	7	9
3	4	5	150	210	270	0	0	30	1	3	5	7	9
1	5	4	80	160	240	320	0	40	2	4	6	8	10
1	5	4	100	200	300	400	0	50	2	4	6	8	10

Macros

Dynamic Processing

Iterative Macro

Accepted answers

Ashish6775

@apathetichell Apologies for not being clear enough.

I have simplified the input and output data now. Assume these are the only columns. You can ignore the cal_day column in the output.

For now, I have used Python tool and wrote a script which replicates the scenario of SAS code.

#################################
from ayx import Package
from ayx import Alteryx

#################################
# read in data from input anchor as a pandas dataframe
# (after running the workflow)
df = Alteryx.read("#1")

#################################
for index, rows in df.iterrows():
    for n in range(df['start_curve'][index], df['end_curve'][index]):
        df['day_calls'+str(df['cal_day'][index])][index] = round(df['dayPct'+str(n)][index]*df['TotalCalls'][index], 0)
        df['cal_day'][index] =  df['cal_day'][index]+1


#################################
# and then send it to one of the output anchors
Alteryx.write(df, 1)

All comments

binu_acs

@Ashish6775 Can you provide the output results of the first row?

apathetichell

You'd use some form of nested macro or you'd use some form of generate rows. basically how you are doing it is a definition of how NOT to do it in Alteryx.. If you can share a few lines (say 3) of your 366 and your 90 (and expected outputs) - I can build this and you can run it for your remaning 366 of (or unrandomized 366 - or whatever).

Ashish6775

I have updated the question with sample input and output. Thank you.

apathetichell

@Ashish6775 Did you update this with sample data? Not seeing it beyond what you initially posted this. Trying to help out here - but if you don't post more comprehensive data it's a waste of our time... How are you getting the new cal_day values in your output? those are the kinds of things. The core x*y is straight forward but without something closer to real data this isn't productive.

Ashish6775

@apathetichell Apologies for not being clear enough.

I have simplified the input and output data now. Assume these are the only columns. You can ignore the cal_day column in the output.

For now, I have used Python tool and wrote a script which replicates the scenario of SAS code.

#################################
from ayx import Package
from ayx import Alteryx

#################################
# read in data from input anchor as a pandas dataframe
# (after running the workflow)
df = Alteryx.read("#1")

#################################
for index, rows in df.iterrows():
    for n in range(df['start_curve'][index], df['end_curve'][index]):
        df['day_calls'+str(df['cal_day'][index])][index] = round(df['dayPct'+str(n)][index]*df['TotalCalls'][index], 0)
        df['cal_day'][index] =  df['cal_day'][index]+1


#################################
# and then send it to one of the output anchors
Alteryx.write(df, 1)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703