Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table with a list of employees in it and another table with a list of dates in it that are auto generated (master calendar table).
What I now need to do is to create a record for each employee with every date in the calendar so as an example:
Employee table:
Bob
Fred
Tim
Pete
Calendar Table:
01-01-01
02-01-01
03-01-01
04-01-01
What I need to end up with is
Results table:
Employee Date
Bob 01-01-01
Bob 02-01-01
Bob 03-01-01
Bob 04-01-01
Fred 01-01-01
Fred 02-01-01
Fred 03-01-01
Fred 04-01-01
Tim 01-01-01
Tim 02-01-01
Tim 03-01-01
Tim 04-01-01
Pete 01-01-01
Pete 02-01-01
Pete 03-01-01
Pete 04-01-01
Is anyone clever enough to help me out with how I might achieve this?
Many thanks in advance,
Ben
With a simple join? (Am I missing something here?)
Employee:
Load * Inline [Employee
Bob
Fred
Tim
Pete
];
CalendarTable:
Join
Load * Inline [Date
01-01-01
02-01-01
03-01-01
04-01-01
];
With a simple join? (Am I missing something here?)
Employee:
Load * Inline [Employee
Bob
Fred
Tim
Pete
];
CalendarTable:
Join
Load * Inline [Date
01-01-01
02-01-01
03-01-01
04-01-01
];
Thanks John,
I was unaware that you could join two unrelated tables to do this but had just found the answer on another post.
This is the code I actually used to do it which has worked just great.
LOAD
Distinct Name as EmployeeName, 0 as EmployeeHours
Resident TimesheetData;
OUTER JOIN
LOAD date(Date) AS EmployeeDate
Resident MasterCalendar;
Appreciate your super quick reply!
Just join the tables, it automatically creates Cartesian table.
RESULTTABLE:
LOAD DISTINCT EMPLOYENAME FROM EMPLOYEE_TABLE;
JOIN (RESULTTABLE)
LOAD DISTINCT DATE FROM CAL_TABLE;