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

[Ashish6775]

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

[binuacs]

@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 · Accepted solution]

@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)