Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Username | Sales_date | Sales_no | Date_difference |
---|---|---|---|
john | 14.01.2013 | 1 | |
john | 17.01.2012 | 2 | 3 |
john | 25.01.2013 | 3 | 8 |
Wade | 10.01.2013 | 1 | |
Wade | 18.01.2013 | 2 | 8 |
Wade | 31.01.2013 | 3 | 13 |
FUID | Sales_date | Sales_no | Date_diffreence |
John | 15.01.2012 | 1 | |
John | 17.01.2012 | 2 | 2 |
John | 23.01.2012 | 3 | 6 |
Wade | 16.01.2012 | 1 | |
Wade | 25.02.2012 | 2 | 40 |
Wade | 03.03.2012 | 3 | 7 |
Fred | 04.04.2012 | 1 | |
Fred | 09.04.2012 | 2 | 5 |
Fred | 24.04.2012 | 3 | 15 |
FUID | Sales_date | Sales_no | Date_diffreence |
John | 15.01.2012 | 1 | |
John | 17.01.2012 | 2 | 2 |
John | 23.01.2012 | 3 | 6 |
Wade | 16.01.2012 | 1 | |
Wade | 25.02.2012 | 2 | 40 |
Wade | 03.03.2012 | 3 | 7 |
Fred | 04.04.2012 | 1 | |
Fred | 09.04.2012 | 2 | 5 |
Fred | 24.04.2012 | 3 | 15 |
FUID | Sales_date | Sales_no | Date_diffreence |
John | 15.01.2012 | 1 | |
John | 17.01.2012 | 2 | 2 |
John | 23.01.2012 | 3 | 6 |
Wade | 16.01.2012 | 1 | |
Wade | 25.02.2012 | 2 | 40 |
Wade | 03.03.2012 | 3 | 7 |
Fred | 04.04.2012 | 1 | |
Fred | 09.04.2012 | 2 | 5 |
Fred | 24.04.2012 | 3 | 15 |
is Your data set ordered by date?
try like this:
LOAD Username,
Sales_date,
if (recno()=1,1,if(Previous(Username)=Username,Previous(Sales_no)+1,1)) as Sales_no,
if(Previous(Username)=Username, Sales_date-Previous(Sales_date),'') as Date_difference
FROM
(ooxml, embedded labels, table is Data_sample);
Thanks.
My dataset was not ordered. I have ordered it by username,date. But it gives error. Becuse, there is no column called sales_no . If I comment sales_no, Date_difference works fine.
Field not found - <Sales_no>
LOAD username,
sales_date,
if (recno()=1,1,if(Previous(username)=username,Previous(Sales_no)+1,1)) as Sales_no,
if(Previous(username)=username, sales_date-Previous(sales_date),'') as Date_difference
FROM
[DATA.QVD](qvd)