Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
;
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.
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