Sort by two dimensions

[Anonymous]

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

];

[Anonymous]

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.

[sumanta12]

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;

[Anonymous]

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;

[antoniotiman]

May be thsi

If(Customer=Above(TOTAL Customer),Interval(date-Above(TOTAL date),'DD'),0)

[Anonymous]

thank you

it's almost that but can you tell me how to limit this interval calculation for each customer?

[Anonymous]

thank you but I would like to keep this calculation in the script

[sumanta12]

Hi Saoud,

If the interval is negative then it will show 0. Is it or something else?

[antoniotiman · Accepted solution]

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;

[Anonymous]

no because it is possible that the delay between two different customers is positive

thanks