Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Sinalize rows that balance sum total to Zero

  1. #1

    Default Sinalize rows that balance sum total to Zero

    I have these data about a bookkepping that was imported and transformed, so I need to sinalize or identify the rows where balance values sum can be zero, like image below:

    Name:  Issue_pentaho_zero.jpg
Views: 53
Size:  37.0 KB

    But I just can sort or group the values by supplier because the data doesn't matter the order or the date.

    Is it possible to do this?

    Thanks.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    So you have an ordered set of financial transactions (deposits and withdrawals) and want to know all pairs of rownumbers (from, to) for which Sum(balance, from, to) = 0?

    I would need Group-By to run a cummulative sum of balance.
    Then I would introduce 2 fields: b1 would be balance * 100 as an Integer, b2 would be 0 - b1.
    The pairs (from, to) would be determined by a Join on b1 = b2.
    By calculating the distance (to - from) I can pick the largest subset.
    I should find a way to identify intersections, too.

    Just an idea, didn't implement it, yet.
    So long, and thanks for all the fish.

  3. #3

    Default

    Sorry for the delay in responding.

    So, I have a set of financial transactions, but they aren't ordered and they usually don't work in pairs. And I want to know all set of transactions where Sum(balance, from, to) = 0.

    E.g.

    Reducing the data in the image in debit sum of the rows num 3,4,6,7 less the row 5 = 0 (1115,76 + 25,45 + 5,79 + 10 = 1157,00) then I want to set "TRUE" or something like.

    But I will try to implement your idea.

    Thanks

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    If the set isn't ordered, you have a knapsack-problem.
    My solution only works for ordered sets.
    So long, and thanks for all the fish.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.