Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kaygee28
Contributor III
Contributor III

Joining Tables in Qlik Not Working

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 .

sales2.PNG

I'm also attaching the two databases.

2 Replies
maxgro
MVP
MVP

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
Contributor III
Contributor III
Author

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.

dates.PNG

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

new data model.PNG