topic Date range in QlikView

Date range

brindlogcool — Tue, 05 Jun 2012 17:48:35 GMT

Hi,

i need to split the date range. Here is the issue i have the table like this

Start Date End Date Userid

01/01/2012 03/31/2012 A

02/01/2012 02/20/2012 B

02/01/2012 03/31/2012 C

01/03/2012 03/31/2012 D

i am trying to split the above table as below in qlikview .

Start Date End Date Userid

01/01/2012 01/31/2012 A

02/01/2012 02/27/2012 A

03/01/2012 03/31/2012 A

02/01/2012 02/20/2012 B

02/01/2012 02/27/2012 C

01/03/2012 03/31/2012 C

01/03/2012 03/31/2012 D

Is there any way we do it in qlikview

Re: Date range

rwunderlich — Wed, 06 Jun 2012 05:43:34 GMT

Do you really need to create a physical table as shown or do you need to distribute the Userid over months?

An easy way to the distribution is with IntervalMatch. Create a table of dates and IntervalMatch the original table against the date table. THen you will have a Userid assigned to every date in it's range. See the QV Cookbook sample "Count days in a transaction using IntervalMatch" for an example of this technique.

If you want to produce an expanded table just like in your example, you could do something like this:

Expanded:

LOAD

Userid,

date(RangeMax(StartDate, MonthStart(AddMonths(StartDate, iterno()-1)))) as xStartDate,

date(RangeMin(EndDate, MonthEnd(AddMonths(StartDate, iterno()-1)))) as xEndDate

RESIDENT data

WHILE AddMonths(StartDate, iterno()-1) <= EndDate

;

-Rob

http://robwunderlich.com

Date range

brindlogcool — Mon, 11 Jun 2012 21:21:49 GMT

Actually i am trying to find the number of days working days - holidays for each month of each row

Consider the first row 01/01/2012 03/31/2012 A i want to find jan month 31 days ,Available days 22 and Feb month the same and March month same... it is same principle even if the end date is 02/01/2012. then it should say the available days is 1.

Start Date End Date Userid

01/01/2012 03/31/2012 A

02/01/2012 02/20/2012 B

02/01/2012 03/31/2012 C

01/03/2012 03/31/2012 D

Re: Date range

rwunderlich — Tue, 12 Jun 2012 04:20:32 GMT

You can calculate the number of workdays with

networkdays(StartDate, EndDate)

You can optionally add a list of holidays as well. See the help for networkdays().

-Rob

Re: Date range

brindlogcool — Tue, 12 Jun 2012 16:18:29 GMT

Thanks Rob, i was able to use the interval match but it is creating the duplicate rows. I am not able to figure the issue and i couldnt share the document in the community

Re: Date range

rwunderlich — Wed, 13 Jun 2012 14:39:10 GMT

Try the method shown above and add a preceding load to calc the workdays.

Expanded:

LOAD *, NetWorkDays(xStartDate,xEndDate) as Workdays

;

LOAD

Userid,

date(RangeMax(StartDate, MonthStart(AddMonths(StartDate, iterno()-1)))) as xStartDate,

date(RangeMin(EndDate, MonthEnd(AddMonths(StartDate, iterno()-1)))) as xEndDate

RESIDENT data

WHILE AddMonths(StartDate, iterno()-1) <= EndDate

;

Re: Date range

brindlogcool — Thu, 14 Jun 2012 00:45:44 GMT

Hi Rob,

I tried using RangeMax but couldnt get the expected results. I was trying with interval match.But getting the below issues.Attached the sample file for your reference.

1. when i reload the data i could see the final table shows as 21 Rows. But in the CTRL+T it is showing 31 Rows

I am not able to find the issue why it is showing 31 rows ..

It is generting the duplicate values if the Date Range is common for two rows(Actually it is a unique row but the date range is common). You can check it in the preview by going to Ctr+T ---- Right click and preview the table (I am sure u know this just i want to make sure where i am checking).

It would be great if you suggest some way to fix it.

Re: Date range

brindlogcool — Tue, 03 Jul 2012 18:32:01 GMT

Hi,

Is there any other way other than the interval match for the below requirement.

i need to split the date range. Here is the issue i have the table like this

Start Date End Date Userid

01/01/2012 03/31/2012 A

02/01/2012 02/20/2012 B

02/01/2012 03/31/2012 C

01/03/2012 03/31/2012 D

i am trying to split the above table as below in qlikview .

Start Date End Date Userid

01/01/2012 01/31/2012 A

02/01/2012 02/27/2012 A

03/01/2012 03/31/2012 A

02/01/2012 02/20/2012 B

02/01/2012 02/27/2012 C

01/03/2012 03/31/2012 C

01/03/2012 03/31/2012 D