Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I would like to know the days between the sales and customer.
I have the following table and I wanna know the days column
Many Thank's
Salesid | Customer | Date | Days |
1 | a | 31/01/2013 | |
2 | b | 31/01/2013 | |
3 | c | 31/01/2013 | |
4 | a | 15/02/2013 | 15 |
5 | b | 20/02/2013 | 20 |
6 | c | 25/02/2013 | 25 |
7 | a | 01/03/2013 | 14 |
8 | b | 10/03/2013 | 18 |
9 | c | 25/03/2013 | 28 |
Hello,
please try this:
Table1:
LOAD * INLINE [
SalesID, Customer, Date
1,a,31/01/2013
2,b,31/01/2013
3,c,31/01/2013
4,a,15/02/2013
5,b,20/02/2013
6,c,25/02/2013
7,a,01/03/2013
8,b,10/03/2013
9,c,25/03/2013
];
noconcatenate
Table2:
load SalesID,
if(previous(Customer) <> Customer, 0, Date-previous(Date)) as Days,
Customer,
Date
resident Table1 order by Customer, Date ASC;
drop table Table1;
Hope this helps.
Hi,
Try this script
Temp:
LOAD
Salesid,
Customer,
Date(Date#(Date, 'DD/MM/YYYY'), 'DD/MM/YYYY') AS Date
Inline [
Salesid, Customer, Date
1, a, 31/01/2013
2, b, 31/01/2013
3, c, 31/01/2013
4, a, 15/02/2013
5, b, 20/02/2013
6, c, 25/02/2013
7, a, 01/03/2013
8, b, 10/03/2013
9, c, 25/03/2013
];
Data:
NoConcatenate
LOAd
*,
If(Previous(Customer) = Customer, Ceil(Num(Date) - Previous(Num(Date)))) AS Days
RESIDENT Temp
ORDER BY Customer, Date;
DROP TABLE Temp;
Regards,
Jagan.
Hello,
please try this:
Table1:
LOAD * INLINE [
SalesID, Customer, Date
1,a,31/01/2013
2,b,31/01/2013
3,c,31/01/2013
4,a,15/02/2013
5,b,20/02/2013
6,c,25/02/2013
7,a,01/03/2013
8,b,10/03/2013
9,c,25/03/2013
];
noconcatenate
Table2:
load SalesID,
if(previous(Customer) <> Customer, 0, Date-previous(Date)) as Days,
Customer,
Date
resident Table1 order by Customer, Date ASC;
drop table Table1;
Hope this helps.
wow!!!!! Great!!! many many thank's!!!