Date range

[brindlogcool]

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

[rwunderlich]

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

[brindlogcool]

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

[rwunderlich]

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

[brindlogcool]

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

[rwunderlich]

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

;

[brindlogcool]

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.

[brindlogcool]

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