Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Fill gap with previous balance

  1. #1
    Join Date
    Apr 2015
    Posts
    5

    Question Fill gap with previous balance

    Hello,

    I have a table with credit, debit and balance for each account number on each 2 weeks.
    The balance is filled only if a credit or a debit operation happens during the 2 weeks, however, in order to perform some calculation, I need to have the balance filled on each row.
    I need to fill the gap using the previous non null balance.
    I have tried to use the "Analytic Query" step, but I can't find a way to make it works in that case.

    I have attached a example of what I have in Input (in the Input tab) and what I expected in output (in the Outputtab) in the following file.
    Sample Fill the balance.xls

    Do you know a way to perform that in PDI ?

    Thanks,

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

    Default

    Set Repeat = Y for the Balance field in your Excel-Input step.
    Read the documentation.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Apr 2015
    Posts
    5

    Default

    Hello,

    Actually, I won't use excel, the sample I gave was an example. I have many steps before the result I showed in the input tab and I will have others after.
    And I am using Table input and output.

    Do you know if there is another step that do that either ?

    Thank you !

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

    Default

    I can't see how Table-Input does suppress repeating Balance values.
    AFAICS Your problem isn't fully specified: It's not clear where your opening Balance comes from.
    Also your sample data is flawed: Balance is changing without any visible transaction in row 25.

    Anyway, Balance is a computed value, so you can just calculate a running total.


    PS: And much easier as I did yesterday, so here is the updated demo.
    Attached Files Attached Files
    Last edited by marabu; 04-16-2015 at 01:50 AM. Reason: improved demo
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Apr 2015
    Posts
    5

    Default

    I have manually created this file to make an example.
    There is no mistake on line 25 (or 50), it's just the beginning balance for the account number.

    I will explain my entire work:
    I have a list of operations (when a customer makes a withdrawal or deposit on his account) visible in the “Operation list” tab of the following file:
    Sample Fill the balance.xls

    From this table I want to have a balance for each 2 weeks of the year, in order to calculate interest amount for each of these 2 weeks period.
    I need to have a table with 24 lines for each account number with the balance.
    Right now I have been able to create a table with 24 lines for each account number and fill the balance for the periods that have an operation (this step is visible in the “Middle step” tab)

    What I need is to apply an iterative LAG on the balance column if its value is 'Null'

    Is it possible to apply the following algorithm:
    Code:
    If (balance is null) then {
                    balance = LAG (balance)
    }

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

    Default

    Sorry, didn't see that.
    You can use Account_Number as a group key in the Group-By step of my demo, then.
    Of course you can use scripting, too. I wouldn't do it, though.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Apr 2015
    Posts
    5

    Default

    All rigth ! It is working perfectly !
    Actually I'd rather use the script because sometime I really need to use the LAG, I don't always have the possibility to calculate the column and this a a function I will use in other case.

    But why do you say you wouldn't do it ? Is it slower ?

    Many thanks !

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

    Default

    I try to avoid traditional coding as part of my dataflow design for simplified documentation and reduced maintenance.
    For others performance seems to be a problem, too.
    So long, and thanks for all the fish.

  9. #9

    Default

    Hey Marabu, Nice solution, very good approach!

    Thanks!

Tags for this Thread

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.