Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

Any idea how to do that ?

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

SalesidCustomerDateDays
1a31/01/2013
2b31/01/2013
3c31/01/2013
4a15/02/201315
5b20/02/201320
6c25/02/201325
7a01/03/201314
8b10/03/201318
9c25/03/201328
1 Solution

Accepted Solutions
salto
Specialist II
Specialist II

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.

View solution in original post

3 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

salto
Specialist II
Specialist II

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.

ecabanas
Creator II
Creator II
Author

wow!!!!! Great!!! many many thank's!!!