Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Range of Date

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:

ContractStartDateEndDate
1Apr-03-2014Apr-05-2014
2Apr-28-2014May-02-2014

Expected data:

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

Thanks for your time,

Ryuma

9 Replies
bbi_mba_76
Partner
Partner

Hi,

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

tresesco
MVP
MVP

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');

Not applicable
Author

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

Not applicable
Author

Hi tresesco,

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

Thank you for your time,

Ryuma

MarcoWedel

Hi Ryuma,


another solution could be:


QlikCommunity_Thread_117376_Pic1.JPG.jpg

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:

ContractStartDateEndDate
104/03/201404/05/2014
204/28/201405/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;

QlikCommunity_Thread_117376_Pic1.JPG.jpg

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:

QlikCommunity_Thread_117376_Pic2.JPG.jpg

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