0

I'm having an issue in implementing the cumulative sum of previous balance with the new balance please help. The Input is below

Date Id Input Spent
20230301 3 1000 200
20230302 3 null 100
20230303 3 500 100

Output Expected

Date Id Input Spent Output
20230301 3 1000 200 800
20230302 3 null 100 700
20230303 3 500 100 1100

Since the Date '20230303' had new input the previous balance should be added with the new input to calculate the Output. Please Advise

Thanks in Advance

TalendDeveloper
  • 35
  • 8
  • What language do you want this solution in, Python or SQL? YOu've tagged multiple technologies here, but i've no idea which you want. If you're using SQL, what's wrong with a windowed `SUM`? – Thom A Mar 14 '23 at 09:15
  • All it seems you want here is a cumulative `SUM` of `Input - Spent`; what is the difficulty you are having here? – Thom A Mar 14 '23 at 09:20
  • I'm looking it in pyspark but should be fine if i get the SQL. Sum(Input-Spent) over a window function doesn't work as Row3 should be sum (previous output + new Input) – TalendDeveloper Mar 14 '23 at 09:25
  • Not sure what you mean it wouldn't work. `(1000 + 500) - (200 + 100 + 100) = 1000 - 200 -100 + 500 - 100 = 1100` which is the value you want. – Thom A Mar 14 '23 at 09:30
  • @Larnu, If you closely check the Expected output table For '20230302' Output value is 700, When Caluclating the next output for '20230303' it should be 700+500(Input) - 100 = 1100 – TalendDeveloper Mar 14 '23 at 09:33
  • I have, and I don't see the problem... You want a cumulative `SUM`, and a cumulative `SUM` gives you the answer, as demonstrated. – Thom A Mar 14 '23 at 09:34
  • `700+500(Input) - 100 = 1100 = (1000 {prior input} - (200 + 100) {prior Spents}) + 500 {Current Input) - 100 {current spent}` It's still a cumulative `SUM`. I suggest you [edit] your question to demonstrate your attempt with a cumulative `SUM` (see the linked duplicate) and explain why it doesn't work. – Thom A Mar 14 '23 at 09:35

0 Answers0