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:
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!
Solved! Go to Solution.
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 |
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!
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
Thanks for all the work.
I don't think I am explaining the problem very well.
As input I have the following:
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:
So for the first twenty weeks the sequence should be the following:
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!
:-) 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
@SeanAdams
That totally makes sense, thank you! I will try to work with the iterative macro to get the logic built in.
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
Wow, this is amazing. Totally makes sense - thank you for all the help!
Seems like using macros is pretty powerful for niche applications.