Hitachi Vantara Pentaho Community Forums
Results 1 to 20 of 20

Thread: Javascript: Calculation based on previous row

  1. #1
    Join Date
    Aug 2010
    Posts
    16

    Default Javascript: Calculation based on previous row

    Hi!

    I am trying to calculate the stock (new column) based on previous row if the Article=prevArticle AND Store=prevStore .

    I have a table with this structure:
    Date;Article;Store;TypeMovement;Qty
    12-02-2008;shoe001;aaa;In;20
    13-02-2008;shoe001;aaa;in;8
    13-02-2008;shoe001;aaa;out;18
    13-02-2008;shoe001;bbb;in;12
    14-02-2008;shoe002;bbb;out;5

    My steps:
    1) New transformation
    2) Use Table Input to get data
    3) Use Analytic Query to create prevArticle and prevStore (LAG "N" rows BACKWARD in get Subject).
    4) Use Modified Java Script Value to calculate stock
    Code:
    var prevStock;
    if (Article==prevArticle && Store==prevStore)
    {
       if (TypeMovement=='In') 
         {
         Stock = prevStock + Qty;
         prevStock=Stock
         }
      else
         {
         Stock = prevStock - Qty;
         }
    }
    else
    {
      if (TypeMovement=='In') 
         {
         Stock =  + Qty;
         }
      else
         {
         Stock = - Qty;
         }
    }
    My problem is to create/update the prevStock value.


    My 'dream' result table output:
    Date;Article;Store;TypeMovement;Qty;Stock
    12-02-2008;shoe001;aaa;In;20;20
    13-02-2008;shoe001;aaa;in;8;28
    13-02-2008;shoe001;aaa;out;18;10
    13-02-2008;shoe001;bbb;in;12;12
    14-02-2008;shoe002;bbb;out;5;5


    Help is welcome

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    I've attached an example that does the stock calculation on some sample data. The intermittent stock values are calculated, and a final step aggregates them to return the current stock values for each item/store.

    Hope that helps

    Cheers

    Slawo
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2010
    Posts
    16

    Default

    Thank you for your help =)

  4. #4
    Join Date
    Sep 2009
    Posts
    810

    Default

    No Problem, we're here to help

    Since similar problems come up every now and then, I have changed your sample a little and have written an article about the techniques used to solve the problem:

    http://type-exit.org/adventures-with...ues-in-kettle/

    It focuses on the fact that you don't really need to access previous row's values using javascript. The "analytic query" step works just fine, and often keeps the JS more readable and concise.

    Cheers

    Slawo

  5. #5
    Join Date
    Feb 2011
    Posts
    840

    Default

    sorry for hijacking this thread, but I'm using Slawo's solution with the Analytic Query Step+User Defined Java Expression, and... it won't work.

    I tried to make it kinda like a "unique rows" step: checking if the previous row's field1 was equal current row's field1... and... for most of my input, it doesn't work. Right from the beginning, it kills some 50 rows - but the first can't be equal a previous, since there is no previous row. Any tips?

    I guess I'm using someone else's solution to stop the transformation for the clone row, using this java code:
    Code:
    trans_Status = CONTINUE_TRANSFORMATION;
    if ((clone_conta==1)){
    	trans_Status = SKIP_TRANSFORMATION
    };
    Analytic Query sends previous "codigoConta" to "prev_conta", UDJE sets clone_conta as below:
    Code:
    Long.valueOf(codigoConta.equals(prev_conta)?0:1)

  6. #6
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi joao,

    Please provide a sample transformation that works on some sample rows from a csv file and attach both files. I'll look into it then.

    Cheers

    Slawo

  7. #7
    Join Date
    Feb 2011
    Posts
    840

    Default

    Wow! That was fast, thanks! I'm attaching a sample xls input, ktr and txt output.
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi Joao,

    I think the formula on the UDJE should be

    Code:
    Long.valueOf(codigoConta.equals(prev_conta)?1:0)
    You want the field to be 1 if they are equal, 0 otherwise. You got this reversed as of now.

    Cheers

    Slawo

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    As I pointed out on Slawo's blog a moment ago, the third solution is to use the "Group By" step and to calculate a "Cumulative Sum" after a Sort operation. The "Include all rows" feature in the "Group By" step has once again been underestimated :-)

  10. #10
    Join Date
    Sep 2009
    Posts
    810

    Default

    Yap, thanks Matt, makes sense,

    That solution will go into the blog post as well. I'm sure I'll find some time this weekend to include the sample

    Cheers

    Slawo

  11. #11
    Join Date
    Feb 2011
    Posts
    840

    Default

    thanks for the speed Slawo! Can't believe I overlooked that.
    I didn't really understood Matt's idea... maybe next week with more time I'll check it out.
    Nice weekend to you both!

  12. #12
    Join Date
    Sep 2009
    Posts
    810

    Default

    Here we go, the blog post got the new script-free solution added, thanks Matt and Joao
    http://bit.ly/i4mKIR

    Cheers

    Slawo

  13. #13
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Thanks for updating the blog post with the sample Slawo. These "code reduction" puzzles are always great fun.
    Pretty much in any situation where data is "grouped" somehow you can eliminate a lot of scripting.

    Cheers,
    Matt

  14. #14
    Join Date
    Feb 2011
    Posts
    840

    Default

    Maybe I'm just not being able to see it... but I don't think this solution would apply to my problem.
    The point is checking if the "running" row is equal to the previous one, and if it is, to drop it instead of processing.

    (never mind about previous problems, I forgot to update some select values steps...)
    Last edited by joao.ciocca; 03-09-2011 at 01:45 PM.

  15. #15
    Join Date
    Sep 2009
    Posts
    810

    Default

    Cool then, was about to look into it

  16. #16
    Join Date
    Feb 2011
    Posts
    840

    Default

    Slawo, I still want to see that other solution! The sample in your blog is still of the old stock.ktr that uses script.
    'Cause I can't see how that change with the cumulative sum would allow me to compare something in row vs row-1...

  17. #17
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It was a specific answer to the specific problem that Slawo put forward on his blog.

  18. #18
    Join Date
    Feb 2011
    Posts
    840

    Default

    oh got it. thanks.

    Btw, I found myself a new minor problem to solve. I need to validate some values... would be something like this:

    account data value
    100 2 300
    200 2 150
    250 3 250
    300 3 100

    There are a lot of accounts, and value is dynamic. What I need is: I have to check if 100's value is equal to [200+250-300]. Ideas? Should I post a new topic?

  19. #19
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi joao,

    I'd like to help, but I don't understand the problem from the description yet. I don't understand what exactly you need to verify. The account and value numbers are so much alike...

    Probably it's also best to open a new topic and name it appropriately. People will find information easier on the forum if threads stick to a single topic, I guess.

    Cheers

    Slawo

  20. #20
    Join Date
    Feb 2011
    Posts
    840

    Default

    hm.. sorry, Ok then I'll open a new thread and explain it better!

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.