# Thread: Javascript: Calculation based on previous row

1. Junior Member
Join Date
Aug 2010
Posts
16

## 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. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
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

3. Junior Member
Join Date
Aug 2010
Posts
16
Thank you for your help =)

4. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
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:

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. Senior Member
Join Date
Feb 2011
Posts
840
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. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
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. Senior Member
Join Date
Feb 2011
Posts
840
Wow! That was fast, thanks! I'm attaching a sample xls input, ktr and txt output.

8. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
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. Chief Data Integration
Join Date
Nov 1999
Posts
9,729
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. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
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. Senior Member
Join Date
Feb 2011
Posts
840
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. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
Here we go, the blog post got the new script-free solution added, thanks Matt and Joao
http://bit.ly/i4mKIR

Cheers

Slawo

13. Chief Data Integration
Join Date
Nov 1999
Posts
9,729
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. Senior Member
Join Date
Feb 2011
Posts
840
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. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
Cool then, was about to look into it

16. Senior Member
Join Date
Feb 2011
Posts
840
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. Chief Data Integration
Join Date
Nov 1999
Posts
9,729
It was a specific answer to the specific problem that Slawo put forward on his blog.

18. Senior Member
Join Date
Feb 2011
Posts
840
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. Pentaho Enterprise Architect
Join Date
Sep 2009
Posts
810
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. Senior Member
Join Date
Feb 2011
Posts
840
hm.. sorry, Ok then I'll open a new thread and explain it better!