Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Running Total from Previous Closing Balance

N89
6 - Meteoroid

Hi

 

Just wondering if someone can help me find a way to Union an old data set with a new data set, pick up the closing balance of the old data set and update the running total for the new data set. 

 

Old Data Set

 

date12Running Balance
1/01/2018171.14 522,878.42
3/01/20183,000,000.00 3,522,878.42
1/02/2018820.75 3,523,699.17
1/03/2018835.91 3,524,535.08
9/03/20183,000,000.00 6,524,535.08
26/03/2018 (600,000.00)5,924,535.08
1/04/20181,655.24 5,926,190.32
1/05/20181,645.33 5,927,835.65
8/05/20182,300,000.00 8,227,835.65
1/06/2018 (2,535.50)8,225,300.15
4/06/20182,400,000.00 10,625,300.15
18/06/20181,300,000.00 11,925,300.15
1/07/20182,532.50 11,927,832.65
 3,722.94 11,931,555.59
 5,071.00 11,936,626.59
1/08/20184,145.55 11,940,772.14
31/08/2018 (2,535.50)11,938,236.64
1/09/20185,759.85 11,943,996.49
27/09/2018 (11,900,000.00)43,996.49
1/10/20184,466.65 48,463.14
3/10/20181,751,536.86 1,800,000.00
 4,248,463.14 6,048,463.14
1/11/20182,805.67 6,051,268.81
1/12/20183,206.69 6,054,475.50
17/12/2018 (1,800,000.00)4,254,475.50

 

New Data Set

date12
1/01/20192499.910
3/01/20190-3500000
4/01/201960000000
17/01/20190-6000000
1/02/20191668.820
14/02/201935000000
1/03/20191367.290
5/03/2019135000000
22/03/20190-11000000
1/04/20197090.240
17/04/20190-6000000
1/05/20192156.210
7/05/2019105000000
21/05/20190-11000000
1/06/20192968.050
1/07/2019136.190
1/08/2019147.640
1/09/2019299.960
1/10/20190-149.98
1/10/2019134.780
1/11/2019142.680
1/12/2019142.810

 

Output

 

date12 Running Balance 
1/01/2018171.14          522,878.42
3/01/20183,000,000.00       3,522,878.42
1/02/2018820.75       3,523,699.17
1/03/2018835.91       3,524,535.08
9/03/20183,000,000.00       6,524,535.08
26/03/2018 (600,000.00)      5,924,535.08
1/04/20181,655.24       5,926,190.32
1/05/20181,645.33       5,927,835.65
8/05/20182,300,000.00       8,227,835.65
1/06/2018 (2,535.50)      8,225,300.15
4/06/20182,400,000.00     10,625,300.15
18/06/20181,300,000.00     11,925,300.15
1/07/20182,532.50     11,927,832.65
 3,722.94     11,931,555.59
 5,071.00     11,936,626.59
1/08/20184,145.55     11,940,772.14
31/08/2018 (2,535.50)    11,938,236.64
1/09/20185,759.85     11,943,996.49
27/09/2018 (11,900,000.00)           43,996.49
1/10/20184,466.65            48,463.14
3/10/20181,751,536.86       1,800,000.00
 4,248,463.14       6,048,463.14
1/11/20182,805.67       6,051,268.81
1/12/20183,206.69       6,054,475.50
17/12/2018 (1,800,000.00)      4,254,475.50
1/01/20192499.910         4,256,975.41
3/01/20190-3500000-       3,500,000.00
4/01/201960000000         6,000,000.00
17/01/20190-6000000-       6,000,000.00
1/02/20191668.820                 1,668.82
14/02/201935000000         3,500,000.00
1/03/20191367.290                 1,367.29
5/03/2019135000000      13,500,000.00
22/03/20190-11000000-     11,000,000.00
1/04/20197090.240                 7,090.24
17/04/20190-6000000-       6,000,000.00
1/05/20192156.210                 2,156.21
7/05/2019105000000      10,500,000.00
21/05/20190-11000000-     11,000,000.00
1/06/20192968.050                 2,968.05
1/07/2019136.190                     136.19
1/08/2019147.640                     147.64
1/09/2019299.960                     299.96
1/10/20190-149.98-                   149.98
1/10/2019134.780                     134.78
1/11/2019142.680                     142.68
1/12/2019142.810                     142.81

 

Initial thoughts are a union and then a multirow formula but i am not sure i have the formula quite correct.

5 REPLIES 5
grazitti_sapna
17 - Castor

Hi @N89 , give this a try and let me know if this works for you.

grazitti_sapna_0-1601441588739.png

 

If this is what you are looking for kindly mark this post as a solution.

Thanks

Sapna Gupta
jdunkerley79
ACE Emeritus
ACE Emeritus

I'd suggest:

jdunkerley79_0-1601445164246.png

 

 

The multi-field formula tool is used to tidy Set 1's numeric value: converting from string to double with an expression of:

IIF(REGEX_Match([_CurrentField_],"\s*"),NULL(),ToNumber(Replace(REGEX_Replace([_CurrentField_],"\((.*)\)","-$1"),",","")))

Next, you pick the last value of this set 

Using an append field tool add the last Running Total to Set 2

You can then use a multi-row formula tool to fill it downward. Set so missing rows are null and then update Running Total with:

IIF(IsNull([Row-1:Running Balance]),[Running Balance],[Row-1:Running Balance])+[1]+[2]

 

Finally, a Union tool with a fixed order of inputs joins the 2 sets together.

N89
6 - Meteoroid

@jdunkerley79 Thanks, your workflow works when i run it but when i include it as part of my larger work flow, the Running Balance throws out all nulls. The append Fields tool works, but then the Multi-Row formula tool doesn't seem to work. Would you happen to know what I can check to make it work? I can't attach the larger workflow as it includes sensitive data. 

 

N89_0-1601955424485.png

N89_1-1601955440907.png

 

 

N89
6 - Meteoroid

@jdunkerley79 All good, i just had to make sure the [1] and [2] columns had 0 not null. Works now. Thanks a lot

jdunkerley79
ACE Emeritus
ACE Emeritus

One other option is to change [1] to IIF(ISNULL([1]),0,[1]) and similar for [2]. This will replace NULLs with 0 as needed.

 

Glad you got it fixed.

Labels