Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ecabanas
Contributor

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
Valued Contributor II

Re: Any idea how to do that ?

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.

3 Replies
MVP
MVP

Re: Any idea how to do that ?

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
Valued Contributor II

Re: Any idea how to do that ?

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
Contributor

Re: Any idea how to do that ?

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

Community Browser