Range of Date

[#1]

Hi,

I am having a problem managing a range of dates and it would be very helpfull any idea I could use to solve it.

This is the problem, I have in a table a "StartDate" column and an "EndDate" Column plus an "Contract" column and I need to flag the days between that range as "Active" value for each date since the "StartDate" but "EndDate" which will be flagged as "Inactive". These flags will let me make a calculation in a line chart of (count of inactive contracts in a month / count of active contracts in same month)

Example of Current data:

Contract	StartDate	EndDate
1	Apr-03-2014	Apr-05-2014
2	Apr-28-2014	May-02-2014

Expected data:

Contract	Date	Status
1	Apr-03-2014	Active
1	Apr-04-2014	Active
1	Apr-05-2014	Inactive
2	Apr-28-2014	Active
2	Apr-29-2014	Active
2	Apr-30-2014	Active
2	May-01-2014	Active
2	May-02-2014	Inactive

Thanks for your time,

Ryuma

[bbi_mba_76]

Hi,

I think youi have to work with the autogenerate function to add the dates between start and endDate.

[tresesco]

Attached sample

LOAD Contract,

     Date(Date#(StartDate,'MMM-DD-YYYY')+IterNo()-1) as Date,

     If(Date(Date#(StartDate,'MMM-DD-YYYY')+IterNo()-1)=Date#(EndDate,'MMM-DD-YYYY'),'InActive', 'Active') as Status

FROM

[http://community.qlik.com/thread/117376]

(html, codepage is 1252, embedded labels, table is @1)

while Date(Date#(StartDate,'MMM-DD-YYYY')+IterNo()-1)<=Date#(EndDate,'MMM-DD-YYYY');

[#4]

Hello,

try to use script like this:

tab:
load * inline [
Contract,StartDate,EndDate
1,2014-04-03,2014-04-05
2,2014-04-28,2014-05-02
];

load Contract, date(StartDate+IterNo()) as st_date, 'Active' as status
Resident tab
While IterNo()<EndDate-StartDate ;

load Contract, EndDate as st_date, 'Inactive' as status
Resident tab;

drop table tab;

regards

Darek

[#5]

Hi tresesco,

Is there any performance difference between the way you solved it and using Autogenerate?

Thank you for your time,

Ryuma

[hic]

See http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC

[MarcoWedel]

Hi Ryuma,

another solution could be:

tabDates:
CrossTable(Status, Date)
LOAD Distinct
  Contract,
  Date(Date#(StartDate, 'MMM-DD-YYYY')+IterNo()-1) as Active,
  Date#(EndDate, 'MMM-DD-YYYY') as Inactive
FROM [http://community.qlik.com/thread/117376]
(html, codepage is 1252, embedded labels, table is @1)
While Date#(StartDate, 'MMM-DD-YYYY')+IterNo()-1<Date#(EndDate, 'MMM-DD-YYYY');

hope this helps

regards

Marco

[MarcoWedel]

... different date format:

Contract	StartDate	EndDate
1	04/03/2014	04/05/2014
2	04/28/2014	05/02/2014

[MarcoWedel]

... shorter script without date formatting:

CrossTable(Status, Date)
LOAD Distinct
  Contract,
  Date(StartDate+IterNo()-1) as Active,
  EndDate as Inactive
FROM [http://community.qlik.com/thread/117376] (html, codepage is 1252, embedded labels, table is @3)
While StartDate+IterNo()<=EndDate;

regards

Marco

[MarcoWedel]

Hi Ryuma,

as you asked for any performance differences:

I compared the different approaches proposed in this thread.

These are the results:

seems like my solution is the slowest.

It also behaved different from the others, i.e. it did not create any rows for contracts that have the same start and end date.

hope this helps

regards

Marco