Difference by Date and Product

[Anonymous]

Hello,

I need to calculate the difference between dates and products.

here i attach you some data.

Date	Product	Value	Difference
05/05/2017	1	34	0
05/05/2017	2	23	0
06/05/2017	1	2	-32
06/05/2017	2	12	-11

From what i have read, it can be done with the peek function, however i always see examples with one dimension.

¿Could you help me?

[sunny_talwar]

You need this in the script or on the front end of the application?

[vinieme12]

try below if you need to do this in script

temp:

load * inline [

Date,Product,Value,Difference

05/05/2017,1,34,0

05/05/2017,2,23,0

06/05/2017,1,2,-32

06/05/2017,2,12,-11

];

NoConcatenate

NEW:

load Product,Date,Value,if(Product=Previous(Product),Value-Peek(Value),0) as Difference resident temp

order by Product,Date;

drop table temp;

[Anonymous]

Thank you Vineeth it makes the job!!

However i would like to understand how it is doing.

¿if instead of two dimensions there are 3?

I have added an employee and the difference is not well calculated. ¿how can i make it?

[sunny_talwar]

Not sure what the output needs to look like, but try this:

temp:

load * inline [

Date,Product,Employee,Value

05/05/2017,1,1,34

05/05/2017,2,2,23

06/05/2017,1,1,2

06/05/2017,2,3,12

];

NoConcatenate

NEW:

load Product,

  Date,

  Value,

  Employee,

  if(Product=Previous(Product) and Employee = Previous(Employee), Value-Peek(Value),0) as Difference

resident temp

order by Product, Employee, Date;

drop Tables temp;

Basically Date should be the last sort for you and if you want to only subtract for the same Employee, then add that condition in your if statement as well as in the order by clause

[Anonymous]

¡thank you, now i understand it!

[Anonymous]

If it´s not a big problem, i would like to know how can it be done the difference only applying it to the same year.

I am asking to know all the possibilities about this function

[sunny_talwar · Accepted solution]

Try this

temp:

load * inline [

Year,Date,Product,Employee,Value

2017,05/05/2017,1,1,34

2017,05/05/2017,2,2,23

2017,06/05/2017,1,1,2

2017,06/05/2017,2,2,27

2017,06/05/2017,2,3,12

2016,05/05/2016,1,1,34

2016,05/05/2016,2,2,23

2016,06/05/2016,1,1,2

2016,06/05/2016,2,2,27

2016,06/05/2016,2,3,12

];

NoConcatenate

NEW:

load Product,

  Date,

  Year,

  Value,

  Employee,

  if(Product=Previous(Product) and Employee = Previous(Employee) and Year = Previous(Year), Value-Peek(Value),0) as Difference

resident temp

order by Product, Employee, Year, Date;

drop Tables temp;