Looping in Data Load to generate table

[LauraMorris]

I am trying to generate a table of employees with a row for the week end date of every week they are employed but it does not appear to be looping (or pulling the Employee id - I get the weekend date of a year ago in both columns)

For i = Weekend(today()-365) To Weekend(today())
Let iDate=Date(i);

TimeSheetsRequired:

LOAD Date($(iDate)) As TimeSheetWeekDate,

"EMPLOY_REF" as EmployeeRef

Resident EmployeLoad

Where STARTDATE <= Date($(iDate)) And ("LAST_WORKING_DAY" >= Date($(iDate) OR ISNULL("LAST_WORKING_DAY"));

[lfetensini]

The problem is: you need all weekends and not all days. "For" loop its incremental by +1. You can use "step 7" but isnt a good practice.

I have a idea:

// Lets create a calendar with all dates first:

LET vDateMin = Num(Today()-365); // Date Min
LET vDateMax = Num(Today());         // Date Max

// Loop to get all dates in a temporary table:
TMP:
LOAD
$(vDateMin) + IterNo() -1                                                    as Date,                      // About 365 different dates
Num( Floor(WeekEnd($(vDateMin) + IterNo() -1)) ) as WeekDate_No   // About 52 different dates
AutoGenerate 1
While IterNo() <= $(DateMax) - $(vDateMin) +1;

// Now we need agroup Weeks to next variable and do "a list" with all
// WeekDates, separated by comma and inside quotation
// Ex: Signal " ' " = chr(39)

TMP2:
LOAD
Concat(distinct (chr(39) & WeekDate_No & chr(39),',') as WeekDates
Resident TMP;

// We have now something like: '45126','45133','45140'...  all weeks

// Lets create a variable with all Weeks from table TMP2:
LET vWeeks = Peek('WeekDates',0,'TMP2');

// Clean tmp tables:
DROP Tables TMP, TMP2;

// Now the magic begins:
FOR EACH vWeekDates in $(vWeeks)
// its like:  For Each vWeekDates in '45126','45133','45140'... 

TimeSheesRequired:
LOAD
Date($(vWeekDates)) as TimeSheetWeekDate,
"EMPLOY_REF"             as EmployeeRef
RESIDENT EmployeLoad
Where STARTDATE <= Date($(vWeekDates)) And ("LAST_WORKING_DAY" >= Date($(vWeekDates) OR ISNULL("LAST_WORKING_DAY"));

NEXT // for return loops in all list

[rwunderlich]

 I would approach it using IntervalMatch.  I'll assume you will  prime the "TimeSheetsRequired" table from your EmployeeLoad table, but I'll show the data Inline for now.

TimeSheetsRequired:
LOAD * Inline [
EmployeeId, StartDate, Last Day Worked
1, 1/2/2019, 8/15/2019
2, 4/15/2012, 5/30/2019
3, 5/1/2019
4, 2/1/2000
]
;

Let vDays = 365; // How many days back
Let vToday = num(Today(1));
WeekendDates:
LOAD Distinct
  Date(Floor(WeekEnd($(vToday) - RecNo() - 1)))
    as [Week Ending Date]
AutoGenerate $(vDays)
;

Join (TimeSheetsRequired)
IntervalMatch ([Week Ending Date])
Load  StartDate, [Last Day Worked]
Resident TimeSheetsRequired
;

DROP Table WeekendDates;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com