Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Autogenerate function

I have a startdate and a enddate of a customer, for example:

Customer     Startdate          Enddate               Value

1                  01-01-2011       15-04-2011          10

1                  16-04-2011       31-12-2011          20

2                  01-01-2011       17-05-2011          15

How can I get the dates between those two datefields for each customer?

1 Solution

Accepted Solutions
disqr_rm
Partner - Specialist III
Partner - Specialist III

Is this what you are looking for?

[code]

Data:

LOAD * INLINE [

Customer, Startdate, Enddate, Value

1, 01/01/2011, 04/15/2011, 10

1, 04/16/2011, 12/31/2011, 20

2, 01/01/2011, 05/17/2011, 15

];

NewData:

LOAD Customer,

     date(Startdate + IterNo()-1) as Date,

     Value

RESIDENT Data

While Startdate + IterNo() <= Enddate+1;

[/code]

View solution in original post

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

To calculate in a dimension:

     =Date#(Enddate, 'dd-mm-yyyy') - Date#(Startdate, 'dd-mm-yyyy;)

However, if you ensure that these values are dates in your load script, like this...

LOAD

     Customer,

     Date(Date#(Startdate, 'dd-mm-yyyy')) AS Startdate,

     Date(Date#(Enddate, 'dd-mm-yyyy')) AS Enddate,

...

FROM ...;

Then

     =Enddate - Startdate

will do the job.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
disqr_rm
Partner - Specialist III
Partner - Specialist III

Is this what you are looking for?

[code]

Data:

LOAD * INLINE [

Customer, Startdate, Enddate, Value

1, 01/01/2011, 04/15/2011, 10

1, 04/16/2011, 12/31/2011, 20

2, 01/01/2011, 05/17/2011, 15

];

NewData:

LOAD Customer,

     date(Startdate + IterNo()-1) as Date,

     Value

RESIDENT Data

While Startdate + IterNo() <= Enddate+1;

[/code]