Update Multiple Fields with Multi-row Formula Tool

[owill]

Hi all,

I am trying to use one calculation to update multiple fields using information from multiple rows. As an illustration, there are three pieces of logic I want to incorporate:

1. I eat three apples a week
2. I can carry 10 apples at a time
3. If I buy 40 apples in a ten week period, I get the last five for free

What I imagine is the following fields:

Number of Apples I Have	Week	Total Apples Purchased
10	1	10
7	2	10
4	3	10
1	4	10
8	5	20
5	6	20
2	7	20
9	8	30
6	9	30
13	10	40
10	0	0
7	0	0
4	0	0
1	0	0
8	1	10
5	2	10
2	3	10
9	4	20

Basically, I need a running total of the number of apples I have purchased so I know how many to buy in week ten. And I need a field that is a re-setting counter from the first purchase so I know when I've reached week ten.

Is there a way to update separate fields as I do a multi-row formula on a single field?

Thank you!

[SeanAdams]

Hey @owill

I don't believe that there's a way to do a multi-row formula, where you update two columns on each row (sadly - this would be a good idea - you should log it under IDEAS https://community.alteryx.com/t5/Alteryx-Product-Ideas/idb-p/product-ideas)

However - in all the cases that I've needed this, I've managed to find away around it by using formulas.   I've mocked this up in Alteryx and attached.   The key here is to do as much as possible using standard formulas.

Number of Apples I Have	Week	Apples Purchased	ApplesPurchasedLast10Weeks	ApplesEaten	FreeApples	NumberRemainingAtEndWeek	NumberApplesDiscarded
10	1	10	0	3	0	17	7
7	2	10	10	3	0	14	4
4	3	10	20	3	0	11	1
1	4	10	30	3	0	8	8
8	5	20	40	3	0	25	15
5	6	20	60	3	5	27	17
2	7	20	80	3	5	24	14
9	8	30	100	3	5	41	31
6	9	30	130	3	5	38	28
13	10	40	160	3	5	55	45
10	0	0	190	3	5	12	2
7	0	0	180	3	5	9	9
4	0	0	170	3	5	6	6
1	0	0	160	3	5	3	3
8	1	10	140	3	5	20	10
5	2	10	130	3	5	17	7
2	3	10	120	3	5	14	4
9	4	20	100	3	5	31	21

[owill]

Hi Sean,

I will post this over there, thanks!

For the problem, I'm assuming we don't discard any (they're magic apples). I would also like the total apples purchased field to reset every time we hit ten weeks. I should have been more clear that I want the table as the output using the three pieces of logic. For instance, I can imagine writing something like the following to generate these rows:

IF
    [Row-1:Week] = 0 && [Row-1:Number of Apples I Have] - 3 >= 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3
    [Week] = 0
    [Apples Purchased] = 0
ELSEIF
    [Row-1:Week] = 0 && [Row-1:Number of Apples I Have] - 3 < 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3 + 10
    [Week] = 1
    [Apples Purchased] = 10
ELSEIF
    [Row-1:Week] < 9 && [Row-1:Week] > 0 && [Row-1:Number of Apples I Have] - 3 >= 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3
    [Week] = [Row-1:Week] + 1
    [Apples Purchased] = [Row-1:Apples Purchased]
ELSEIF
    [Row-1:Week] < 9 && [Row-1:Week] > 0 && [Row-1:Number of Apples I Have] - 3 < 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3 + 10
    [Week] = [Row-1:Week] + 1
    [Apples Purchased] = [Row-1:Apples Purchased] + 10
ELSEIF
    [Row-1:Week] = 9 && [Row-1:Apples Purchased] < 40
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3 + 40 – [Row-1:Apples Purchased]
    [Week] = [Row-1:Week] + 1
    [Apples Purchased] = 40
ELSEIF
    [Row-1:Week] = 9 && [Row-1:Number of Apples I Have] – 3 >= 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3
    [Week] = [Row-1:Week] + 1
    [Apples Purchased] = [Row-1:Apples Purchased]
ELSEIF
    [Row-1:Week] = 9 && [Row-1:Number of Apples I Have] – 3 < 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3 + 10
    [Week] = 1
    [Apples Purchased] = 10
ELSEIF
    [Row-1:Week] = 10 && [Row-1:Number of Apples I Have] – 3 >= 0
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3
    [Week] = 0
    [Apples Purchased] = 0
ELSE
    "[Row-1:Week] = 10 && [Row-1:Number of Apples I Have] – 3 < 0"
THEN
    [Number of Apples I Have] = [Row-1:Number of Apples I Have] – 3 + 10
    [Week] = 1
    [Apples Purchased] = 10
ENDIF

Hope that makes sense as I see it is a little dense.

Thanks for the help!

[SeanAdams]

Hey @owill

Made some updates based on your notes - screenshot; alteryx flow; and detailed workings below.   Note: I would avoid trying to code complex if-then code logic, you can do this much more elegantly in Alteryx.

Here's what's going on:

Tool 1: Creates a weekID column which counts the week ID seen so far.   So the first week will be 1, second will be 2, and so on.   This will be helpful later

Resulting data from tool1:

WeekID	Number of Apples I Have	Week	Apples Purchased
1	10	1	10
2	7	2	10
3	4	3	10
4	1	4	10
5	8	5	20
6	5	6	20
7	2	7	20
8	9	8	30
9	6	9	30
10	13	10	40
11	10	0	0
12	7	0	0
13	4	0	0
14	1	0	0
15	8	1	10
16	5	2	10
17	2	3	10
18	9	4	20

Tool 2 & 3: 

Given that you want to think in 10 week periods, I've created a new column that tells you what 10 week period you're in, and what specific week you are in during that 10 week period.

So, for example- weekID 1 is period 1; week1.   WeekID number 15 is period 2; week 5 in that period.   you can see these as the last two columns in this set below

Resulting data from tool 2&3:

WeekID	Number of Apples I Have	Week	Apples Purchased	10WeekShoppingPeriod	ShoppingCounterWeekID
1	10	1	10	1	1
2	7	2	10	1	2
3	4	3	10	1	3
4	1	4	10	1	4
5	8	5	20	1	5
6	5	6	20	1	6
7	2	7	20	1	7
8	9	8	30	1	8
9	6	9	30	1	9
10	13	10	40	1	10
11	10	0	0	2	1
12	7	0	0	2	2
13	4	0	0	2	3
14	1	0	0	2	4
15	8	1	10	2	5
16	5	2	10	2	6
17	2	3	10	2	7
18	9	4	20	2	8

Tool 4: Calculate the total purchase count in the 10 week period using a multi-row formula - you can see this in the last column below.   Note - you can do this with a summarise tool (grouping on the 10WeekShoppingPeriod column, and then join this back to the data if you wanted instead)

Because I reset the counter every time the 10WeekShoppingPeriod changes - it resets to zero (or the appropriate sum) by definition

Resulting data from tool 4:

WeekID	Number of Apples I Have	Week	Apples Purchased	10WeekShoppingPeriod	ShoppingCounterWeekID	ApplesPurchasedLast10Weeks
1	10	1	10	1	1	10
2	7	2	10	1	2	20
3	4	3	10	1	3	30
4	1	4	10	1	4	40
5	8	5	20	1	5	60
6	5	6	20	1	6	80
7	2	7	20	1	7	100
8	9	8	30	1	8	130
9	6	9	30	1	9	160
10	13	10	40	1	10	200
11	10	0	0	2	1	0
12	7	0	0	2	2	0
13	4	0	0	2	3	0
14	1	0	0	2	4	0
15	8	1	10	2	5	10
16	5	2	10	2	6	20
17	2	3	10	2	7	30
18	9	4	20	2	8	50

Tool 5: 

Here we calculate

- the number of apples eaten per week (defined as 3 in your brief);

- The number of free apples (for any week where it's the 10th week in the period, and I've purchased more than 40 so far in the month, give me 5 free)

- the number that remain at the end of the week (starting value + purchases + free apples - eaten apples)

Note: I had included a "Discarded Apples" column because your original brief said that you could only carry 10 apples at a time, so if you got to Friday with 7 you could start on Monday with 7 - but if you got to Friday with 25 you wouldn't be able to carry the other 15, and could only start the week on Monday with a maximum of 10.    However - I've left the column (your reply about them being Magic), and made the column zero

Resulting data from tool 5:

WeekID	Number of Apples I Have	Week	Apples Purchased	10WeekShoppingPeriod	ShoppingCounterWeekID	ApplesPurchasedLast10Weeks	ApplesEaten	FreeApples	NumberRemainingAtEndWeek	NumberApplesDiscarded
1	10	1	10	1	1	10	3	0	17	0
2	7	2	10	1	2	20	3	0	14	0
3	4	3	10	1	3	30	3	0	11	0
4	1	4	10	1	4	40	3	0	8	0
5	8	5	20	1	5	60	3	0	25	0
6	5	6	20	1	6	80	3	0	22	0
7	2	7	20	1	7	100	3	0	19	0
8	9	8	30	1	8	130	3	0	36	0
9	6	9	30	1	9	160	3	0	33	0
10	13	10	40	1	10	200	3	5	55	0
11	10	0	0	2	1	0	3	0	7	0
12	7	0	0	2	2	0	3	0	4	0
13	4	0	0	2	3	0	3	0	1	0
14	1	0	0	2	4	0	3	0	-2	0
15	8	1	10	2	5	10	3	0	15	0
16	5	2	10	2	6	20	3	0	12	0
17	2	3	10	2	7	30	3	0	9	0
18	9	4	20	2	8	50	3	0	26	0

What's next

So - depending on where you want to go next with this data - you should have a very good framework to start adding on - all without needing a multi-value-multi-row formula tool.

For example -

- if you wanted to set the next week's starting value based on the previous week's ending value - that's a simple multi-row formula at the end

- if you wanted to remove 10% of the apples that are still in the house when you get to Friday (really bad squirrels or mice invade your house every weekend) - that's a simple formula 

etc.

If I'm still missing the key challenge here - best way to get to a final solution would be to mock up the input data (what you start with before doing any work) and the output data (the output you want this workflow to deliver) and the basic rules in the middle in the way that @JoeM does in the weekly challenges (see here for an example - https://community.alteryx.com/t5/Weekly-Challenge/Challenge-1-Join-to-Range/m-p/36621#U36621).   BTW - I can't say enough good things about doing the weekly challenges - best training ever!

Alternatively - if this has solved your problem - would you mind marking this thread as solved?

Cheers

Sean

[owill]

Thanks for all the work.

I don't think I am explaining the problem very well.

As input I have the following:

• Apples are eaten every day (-3)
• Apples only get ordered when I run out (+10)
• Apples get purchased at week 10 to make sure I bought 40 total in ten week timeframe (+ 40 - apples purchased in last 9 weeks)

Week	Apples Eaten	Apple Order Size	10 Week Apple Discount
1	0	10	40
2	3	10	40
3	3	10	40
4	3	10	40
5	3	10	40
6	3	10	40
7	3	10	40
8	3	10	40
9	3	10	40
10	3	10	40
11	3	10	40
12	3	10	40
13	3	10	40
14	3	10	40
15	3	10	40
16	3	10	40
17	3	10	40
18	3	10	40
19	3	10	40

The only output I really want to get is:

Number of Apples At the End of the Week
10
7
4
1
8
5
2
9
6
13
10
7
4
1
8
5
2
9
6

The pieces of logic I want to put in are:

1. Don't buy more than you need
2. Buy in quantities of 10, unless you buy extra in week 10
3. Ten weeks after an initial purchase, buy up to 40
4. Only buy again when you need to after buying extra from week 10
5. Once you buy again, your next 10 week period starts

So for the first twenty weeks the sequence should be the following:

• Week 1 - Buy ten apples (10) <-- Start of first ten week window
• Week 2 - Eat three apples (7)
• Week 3 - Eat three apples (4)
• Week 4 - Eat three apples (1)
• Week 5 - Buy ten apples, eat three apples (8)
• Week 6 - Eat three apples (5)
• Week 7 - Eat three apples (2)
• Week 8 - Buy ten apples, eat three apples (9)
• Week 9 - Eat three apples (6)
• Week 10 - Buy ten apples, eat three apples (13) <-- To get 10 week discount
• Week 11 - Eat three apples (10)
• Week 12 - Eat three apples (7)
• Week 13 - Eat three apples (4)
• Week 14 - Eat three apples (1)
• Week 15 - Buy ten apples, eat three apples (8) <-- Start of new 10 week window (have to buy total of 40 by week 24)
• Week 16 - Eat three apples (5)
• etc.

The logic is convoluted, but does that make sense? I thought the multiple fields (Apples Purchased & Week) would be the best way to keep track of how many I've bought and when I have to purchase the additional apples at the end of a given 10 week period.

Thanks for all the help!

[SeanAdams]

:-) I think I have the logic now:

- Week 1: eat zero and stock up for the apple-eating to come

- Every week thereafter

     - eat 3 apples

     - if you don't have 3 to eat, buy 10

     - In some weeks you can buy a bunch more apples to hit the quantity discount, according to the following logic:

                      The first time you buy apples, count this as discount week 1

                      Every week there after is discount week 2, 3, etc

                      In discount week 10, you can buy up to 40 apples to hit a cumulative total of 40 since discount week 1 (to hit discount pricing)

                      Whatever you buy in Discount week 10 - the discount week counter resets to zero until you buy again, then it starts from 1.

I think I now understand why you were struggling with the double multi-field-formula - I'm going to tackle this with an iterative macro which is the simplest way to loop through stuff until you hit a particular outcome.   An iterative macro can mimic the looping structure you're looking for, and it allows you to do multiple things on each loop iteration.

Hopefully get this done tonight, or tomorrow morning

Cheers @owill

Sean

[owill]

@SeanAdams

That totally makes sense, thank you! I will try to work with the iterative macro to get the logic built in.

[SeanAdams · Accepted solution]

Here you go.

I've done this in a very explicit way - every formula broken out so that you don't have to decipher it - you could probably compact this a lot with a little thought, but here it is, and I've validated that it matches your data exactly, and also run it with a random input set.

The trick with Iterative Macros is getting your head around the idea that the input for the next round is the output from this - so the way that this works is basically:

- Do a bunch of calculations on the first week, figuring out what all the closing balances and counters are

- Pop these into the starting balance for the second week

- Send the first week to the output, and then send all other weeks to the "rework" process

That way, your record set gets 1 row shorter every iteration, and outputs 1 row every iteration.

This should solve your problem and hopefully give you an example that you can reuse in other challenges.  If so - would you mark as "Solved"?

Cheers

Sean

[owill]

Wow, this is amazing. Totally makes sense - thank you for all the help!

Seems like using macros is pretty powerful for niche applications.

[SeanAdams]

No worries at all - it was a nice challenge to help stretch my own thinking.

If I can recommend - @JoeM has a fantastic series of weekly challenges, and I've really found that they accelerated my learning (just look at the training tab on the left). If you're keen to start working through these, I'd recommend you do the following:

- Take a look at the list the Joe publishes - this contains a tag to say which are easy; intermediate; hard - list here: https://community.alteryx.com/t5/Weekly-Challenge/Weekly-Challenge-Index-amp-Welcome/td-p/48275

- Pick one of the beginner ones, and download the start file. For example - number 2; or 8 or 9 or 15

- Battle through this until you have a solution that works - no matter how awful your solution may be :)

- Submit your answer to the discussion board

- Then, once you've closed your solution - download @JoeM's solution; or some of the others on the forum - and try to understand how others have tackled this (if I see something novel, I often go-back and try to replicate this myself).

I've found that this is a powerful way to learn because it forces you to struggle for your own solution, and once you've got your head around the problem you have fertile soil to learn a few other tips and tricks.

I can't say enough about the learning power of the weekly challenges - check them out here, and I'll look out for some of your solutions on these challenges as you work through them: https://community.alteryx.com/t5/Weekly-Challenge/Weekly-Challenge-Index-amp-Welcome/td-p/48275

Cheers
Sean