topic Re: Any idea how to do that ? in QlikView

Any idea how to do that ?

ecabanas — Wed, 11 Dec 2013 09:16:00 GMT

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

Re: Any idea how to do that ?

jagan — Wed, 11 Dec 2013 09:27:29 GMT

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.

Re: Any idea how to do that ?

salto — Wed, 11 Dec 2013 09:39:24 GMT

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.

Re: Any idea how to do that ?

ecabanas — Wed, 11 Dec 2013 10:23:44 GMT

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