Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
Highlighted
bbi_mba_76
Valued Contributor

Re: Range of Date

Hi,

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

MVP
MVP

Re: Range of Date

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

Re: Range of Date

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

Re: Range of Date

Hi tresesco,

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

Thank you for your time,

Ryuma

Re: Range of Date

Re: Range of Date

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

Re: Range of Date

... different date format:

ContractStartDateEndDate
104/03/201404/05/2014
204/28/201405/02/2014

Re: Re: Range of Date

... 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

Re: Range of Date

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