Joining Tables in Qlik Not Working

[kaygee28]

Good day All

I have a huge problem which I cant seem to resolve simply becuase I generated a master calendar from two tables which had dates the employees table and the sales table. The two table both have Employee ID as the primary key but the sales amounts from the sales table do not map to the employee names through the Employee ID, to demonstrate here is my code below:

LOAD [Employee ID],

     [First name],

     [Last name],

     [Hire date] as DateField,

     [Temp worker],

     [Position ID],

     [Department ID],

     'Employee' as Flag,

     [Office ID],

     [Manager ID]

FROM

(ooxml, embedded labels, table is Employee);

Join Sales:

LOAD [Sales Date] as DateField,

     [Employee ID],

     'Sales' as Flag,

     [Sales Amount]

FROM

(ooxml, embedded labels, table is Sales);

Calendar:

LOAD Date(DateField) as DateField,

     Year(DateField) as Year,

     'Q' & Ceil(Month(DateField)/3) as Quarter,

     Month(DateField) as Month,

     Day(DateField) as Day,

     Week(DateField) as Week;

LOAD Date(MinDate + Iterno() -1) as DateField

     While(MinDate + Iterno() -1)<=Num(MaxDate);

LOAD Min(DateField) as MinDate,

     Max(DateField) as MaxDate

Resident Employee;

the image below also demonstrates that the sales amounts don't get mapped to the employees at all, it's like the sales table created it's own external table within the employees .

I'm also attaching the two databases.

[maxgro]

I think you can

- remove the flag fields and the join,

- don't alias the date fields

- decide if you need one or two calendar

Canonical Date

LOAD

     [Employee ID],

     [First name],

     [Last name],

     [Hire date], as DateField,

     [Temp worker],

     [Position ID],

     [Department ID],

     'Employee' as Flag,

     [Office ID],

     [Manager ID]

FROM (ooxml, embedded labels, table is Employee);

Join Sales:

LOAD

     [Sales Date],  as DateField,

     [Employee ID],

     'Sales' as Flag,

     [Sales Amount]

FROM (ooxml, embedded labels, table is Sales);

[kaygee28]

Hi I used the canonical date method with three master calendars now, but the model isn't reading my dates at all so I'm assuming it's cause I don't have that fine grain table.

See my code below:

Employee:

LOAD [Employee ID],

     [First name],

     [Last name],

     [Hire date],

     [Temp worker],

     [Position ID],

     [Department ID],

     [Office ID],

     [Manager ID]

FROM

(ooxml, embedded labels, table is Employee);

Sales:

LOAD [Sales Date],

     [Employee ID],

     [Sales Amount]

FROM

(ooxml, embedded labels, table is Sales);

DateBridge:

     Load [Employee ID], Applymap('[Employee ID]2[Hire Date]',[Employee ID],Null()) as CanonicalDate, 'Employee' as DateType

     Resident Employee;

     Load [Employee ID], Applymap('[Employee ID]2[Sales Date]',[Employee ID],Null()) as CanonicalDate, 'Sales' as DateType

     Resident Sales;

Master_Calendar:

LOAD Date([CanonicalDate]) as CanonicalDate,

     Year(CanonicalDate) as Year,

     'Q' & Ceil(Month(CanonicalDate)/3) as Quarter,

     Month(CanonicalDate) as Month,

     Day(CanonicalDate) as Day,

     Week(CanonicalDate) as Week;

LOAD Date(MinDate + Iterno() -1) as CanonicalDate

     While(MinDate + Iterno() -1)<=Num(MaxDate);

LOAD Min(CanonicalDate) as MinDate,

     Max(CanonicalDate) as MaxDate

Resident DateBridge;

See for instance table below which doesn't display any dates.

And this is my subsequent data model schema which is being made complicated by the databridge.