Running Total from Previous Closing Balance

[N89]

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

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

New Data Set

date	1	2
1/01/2019	2499.91	0
3/01/2019	0	-3500000
4/01/2019	6000000	0
17/01/2019	0	-6000000
1/02/2019	1668.82	0
14/02/2019	3500000	0
1/03/2019	1367.29	0
5/03/2019	13500000	0
22/03/2019	0	-11000000
1/04/2019	7090.24	0
17/04/2019	0	-6000000
1/05/2019	2156.21	0
7/05/2019	10500000	0
21/05/2019	0	-11000000
1/06/2019	2968.05	0
1/07/2019	136.19	0
1/08/2019	147.64	0
1/09/2019	299.96	0
1/10/2019	0	-149.98
1/10/2019	134.78	0
1/11/2019	142.68	0
1/12/2019	142.81	0

Output

date	1	2	Running Balance
1/01/2018	171.14		522,878.42
3/01/2018	3,000,000.00		3,522,878.42
1/02/2018	820.75		3,523,699.17
1/03/2018	835.91		3,524,535.08
9/03/2018	3,000,000.00		6,524,535.08
26/03/2018		(600,000.00)	5,924,535.08
1/04/2018	1,655.24		5,926,190.32
1/05/2018	1,645.33		5,927,835.65
8/05/2018	2,300,000.00		8,227,835.65
1/06/2018		(2,535.50)	8,225,300.15
4/06/2018	2,400,000.00		10,625,300.15
18/06/2018	1,300,000.00		11,925,300.15
1/07/2018	2,532.50		11,927,832.65
	3,722.94		11,931,555.59
	5,071.00		11,936,626.59
1/08/2018	4,145.55		11,940,772.14
31/08/2018		(2,535.50)	11,938,236.64
1/09/2018	5,759.85		11,943,996.49
27/09/2018		(11,900,000.00)	43,996.49
1/10/2018	4,466.65		48,463.14
3/10/2018	1,751,536.86		1,800,000.00
	4,248,463.14		6,048,463.14
1/11/2018	2,805.67		6,051,268.81
1/12/2018	3,206.69		6,054,475.50
17/12/2018		(1,800,000.00)	4,254,475.50
1/01/2019	2499.91	0	4,256,975.41
3/01/2019	0	-3500000	-       3,500,000.00
4/01/2019	6000000	0	6,000,000.00
17/01/2019	0	-6000000	-       6,000,000.00
1/02/2019	1668.82	0	1,668.82
14/02/2019	3500000	0	3,500,000.00
1/03/2019	1367.29	0	1,367.29
5/03/2019	13500000	0	13,500,000.00
22/03/2019	0	-11000000	-     11,000,000.00
1/04/2019	7090.24	0	7,090.24
17/04/2019	0	-6000000	-       6,000,000.00
1/05/2019	2156.21	0	2,156.21
7/05/2019	10500000	0	10,500,000.00
21/05/2019	0	-11000000	-     11,000,000.00
1/06/2019	2968.05	0	2,968.05
1/07/2019	136.19	0	136.19
1/08/2019	147.64	0	147.64
1/09/2019	299.96	0	299.96
1/10/2019	0	-149.98	-                   149.98
1/10/2019	134.78	0	134.78
1/11/2019	142.68	0	142.68
1/12/2019	142.81	0	142.81

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

[grazitti_sapna · Accepted solution]

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

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

Thanks

[jdunkerley79 · Accepted solution]

I'd suggest:

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]

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

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

[jdunkerley79]

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.