Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a script with clients and dates and I would like to get a table sort by clients and dates. Then use the above function to calculate a delay between each customer's date. Is this possible? Thank you for your help
Data :
load* Inline
[Customer, date, order, amount
4189,17/03/2015,45988038,73
6745,22/10/2017,46351840,66
5607,12/08/2016,46237091,83
1000000963,02/06/2016,32897484,105
1000000369,22/12/2017,46296443,97
6745,05/03/2017,32503124,116
1000000963,02/06/2016,32897484,105
400483848,23/05/2017,46102582,33
1000000369,03/07/2017,32602883,99
1000000369,08/05/2016,32721423,102
1000000593,18/05/2017,32342821,26
1000000369,06/07/2015,32909680,149
5607,13/08/2017,46238408,61
4189,22/10/2017,46351840,66
1000000593,19/05/2017,32342821,26
1000000963,02/06/2016,32897484,105
4189,22/10/2017,46351840,66
1000001048,25/08/2017,32694333,160
];
Data:
Load * From ...;
NoConcatenate
LOAD *,If(Customer=Peek(Customer),Interval(date-Peek(date),'DD'),0) as Diff
Resident Data
Order By Customer,date;
Drop Table Data;
Just add this below:
Data_sorted:
NoConcatenate LOAD * Resident Data Order by Customer,date;
Then you would be able to calculate the margins between the dates.
Hi,
Try this:
Data :
load * Inline [
Customer,date,order,amount
4189,17/03/2015,45988038,73
6745,22/10/2017,46351840,66
5607,12/08/2016,46237091,83
1000000963,02/06/2016,32897484,105
1000000369,22/12/2017,46296443,97
6745,05/03/2017,32503124,116
1000000963,02/06/2016,32897484,105
400483848,23/05/2017,46102582,33
1000000369,03/07/2017,32602883,99
1000000369,08/05/2016,32721423,102
1000000593,18/05/2017,32342821,26
1000000369,06/07/2015,32909680,149
5607,13/08/2017,46238408,61
4189,22/10/2017,46351840,66
1000000593,19/05/2017,32342821,26
1000000963,02/06/2016,32897484,105
4189,22/10/2017,46351840,66
1000001048,25/08/2017,32694333,160
];
DATA1:
LOAD
*,
date-PREVIOUS(date) AS INTERVAL
RESIDENT Data ORDER BY Customer,date;
DROP TABLE Data;
This would calculate the interval regardless of the customer, so in addition I would suggest the following:
DATA1:
NoConcatenate LOAD *,
if(customer=Previous(Customer),date-Previous(date),0) as INTERVAL
Resident Data ORDER By Customer, date;
Drop Table Data;
May be thsi
If(Customer=Above(TOTAL Customer),Interval(date-Above(TOTAL date),'DD'),0)
thank you
it's almost that but can you tell me how to limit this interval calculation for each customer?
thank you but I would like to keep this calculation in the script
Hi Saoud,
If the interval is negative then it will show 0. Is it or something else?
Data:
Load * From ...;
NoConcatenate
LOAD *,If(Customer=Peek(Customer),Interval(date-Peek(date),'DD'),0) as Diff
Resident Data
Order By Customer,date;
Drop Table Data;
no because it is possible that the delay between two different customers is positive
thanks