Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
You need this in the script or on the front end of the application?
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;
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?
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
¡thank you, now i understand it!
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
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;