Join two unrelated tables to create a record for every date?

[canoebi99]

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

[johnca · Accepted solution]

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

[canoebi99]

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!

[#4]

Just join the tables, it automatically creates Cartesian table.

RESULTTABLE:

LOAD DISTINCT EMPLOYENAME FROM EMPLOYEE_TABLE;

JOIN (RESULTTABLE)

LOAD DISTINCT DATE FROM CAL_TABLE;