Interval Match ( I think )

[racer25]

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

[maxgro · Accepted solution]

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;