Interval Match ( I think )

racer25 — Thu, 26 Mar 2015 15:48:21 GMT

Hi All,

I have spent the last hour or so trying to figure this out with the help of the Community Pages and I finally call surrender.

I have a range of dates for various employees and an amount of time they are required to complete each week. Later in the script I have a time entry load which I intend to link to a time requirement table.

My script is below where I load the requirements table 1st and then a calendar. I am looking to create a year, week, USERID key.

The Start date and End date span several years.

Would someone please show me the way to expand the table to include for each user a year/week for every week in between START and END.

Thanks in advance,

Rob

USER_REQ:

LOAD "TCC_USER" as USERID,

floor("DATE_START") as START,

Floor(if(Len("DATE_END")=0,monthend(today()),"DATE_END")) as END,

HOURS;

SQL SELECT *

FROM Qlikview.dbo."tbl_Time_Requirements";

Let vStartDate=Floor(MakeDate(2014,1,1));

Let vEndDate=Floor(MonthEnd(Today()));

Let vDiff=vEndDate-vStartDate+1;

Calendar:

Load

DATE as DATEID,

Date(DATE) As DATE,

Day(DATE) as Day,

Week(DATE) as Week,

Month(DATE) as Month,

Year(DATE) As Year,

WeekYear(DATE) as WeekYear,

WeekYear(DATE) & '-' & Num(Week(DATE), '00') As YearWeek,

Date(MonthStart(DATE), 'YYYY-MMM') As YearMonth

;

Load

RecNo()-1+$(vStartDate) As DATE

AutoGenerate($(vDiff));

Re: Interval Match ( I think )

maxgro — Thu, 26 Mar 2015 21:08:48 GMT

just an idea to expand the table with years and weeks (hope to understand)

source:

load * inline [

userid, start, end

1, 1/1/2015, 31/03/2015

2, 1/6/2012, 31/07/2012

3, 1/10/2012, 31/12/2012

4, 1/1/2011, 31/12/2011

];

final:

load distinct userid, Year(date) as year, Week(date) as week;

load

date(start + iterno()-1) as date

Resident source

While (start + IterNo() -1) <= end;

DROP Table source;

topic Interval Match ( I think ) in QlikView

Interval Match ( I think )

Re: Interval Match ( I think )