topic Re: Range of Date in QlikView

Range of Date

Tue, 06 May 2014 12:45:16 GMT

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

Re: Range of Date

bbi_mba_76 — Tue, 06 May 2014 12:49:29 GMT

Hi,

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

Re: Range of Date

tresB — Tue, 06 May 2014 12:57:32 GMT

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

Re: Range of Date

Tue, 06 May 2014 13:05:11 GMT

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

Re: Range of Date

Tue, 06 May 2014 13:17:31 GMT

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

hic — Tue, 06 May 2014 18:35:37 GMT

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

HIC

Re: Range of Date

MarcoWedel — Tue, 06 May 2014 22:56:59 GMT

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

Re: Range of Date

MarcoWedel — Tue, 06 May 2014 23:27:59 GMT

... different date format:

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

Re: Re: Range of Date

MarcoWedel — Tue, 06 May 2014 23:37:37 GMT

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

Re: Range of Date

MarcoWedel — Wed, 07 May 2014 22:46:25 GMT

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