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

Linking two calendars into a master calendar

Good day All, I have two calendars which I intend on merging into a master calendar so that I can avoid having synthentic keys in my data model. The first calendar has the hire date of an employee and the second calendar has the salesdate of each employee and I initially looked at this articleCanonical Datebut there is no fine grain table between the two calendars so I was wondering whether there might be an alternative to doing this.

Here is my code below I cut it down to make things alot easier to read:

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

Employee_Cal:

LOAD Date([Hire date]) as [Hire date],

     Year([Hire date]) as Year,

     'Q' & Ceil(Month([Hire date])/3) as Quarter,

     Month([Hire date]) as Month,

     Day([Hire date]) as Day,

     Week([Hire date]) as Week;

LOAD Date(MinDate + Iterno() -1) as [Hire date]

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

LOAD Min([Hire date]) as MinDate,

     Max([Hire date]) as MaxDate

Resident Employee;

Sales:

LOAD [Sales Date],

     [Employee ID],

     [Sales Amount]

FROM

(ooxml, embedded labels, table is Sales);

Sales_Cal:

LOAD Date([Sales Date]) as [Sales Date],

     Year([Sales Date]) as Year,

     'Q' & Ceil(Month([Sales Date])/3) as Quarter,

     Month([Sales Date]) as Month,

     Day([Sales Date]) as Day,

     Week([Sales Date]) as Week;

LOAD Date(MinDate + Iterno() -1) as [Sales Date]

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

LOAD Min([Sales Date]) as MinDate,

     Max([Sales Date]) as MaxDate

Resident Sales;

Now how do I get the Employee+Cal and Sales_cal into a master calendar in this case?

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Does this helps?

Employee:

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

[..\QlikView Assessment.xlsx]

(ooxml, embedded labels, table is Employee);

Join

Sales:

LOAD [Sales Date] as DateField,

    [Employee ID],

    'Sales' as Flag,

    [Sales Amount]

FROM

[..\QlikView Assessment.xlsx]

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

12 Replies
Anil_Babu_Samineni

Can you share those 2 excel files also? we will look into this.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kaygee28
Contributor III
Contributor III
Author

Hi Anil, thanks for the response please see attached two files.

Thanks in advance.

Anil_Babu_Samineni

Does this helps?

Employee:

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

[..\QlikView Assessment.xlsx]

(ooxml, embedded labels, table is Employee);

Join

Sales:

LOAD [Sales Date] as DateField,

    [Employee ID],

    'Sales' as Flag,

    [Sales Amount]

FROM

[..\QlikView Assessment.xlsx]

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kaygee28
Contributor III
Contributor III
Author

Hi Anil this works perfectly all I did afterwards was to create a listbox that flagged which table I was referring to and then created an alternate state for different pages, this was so helpful I really appreciate it!!

kaygee28
Contributor III
Contributor III
Author

Hi Anil one problem though it seems like the tables didn't map using the product key which is the Employee ID instead they used the DateField as a product key it seems, cause I can't match the employee names to the sales amounts or is it something else rather?

sales.PNG

Anil_Babu_Samineni

You may select Sales from Flag filter and check data retrieves correct or not?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kaygee28
Contributor III
Contributor III
Author

Hi Anil unfortunately not, so not sure what might be needed here?

sales2.PNG

Anil_Babu_Samineni

There is no data for that, Could be the reason behind we are not getting data for First and Last Names ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kaygee28
Contributor III
Contributor III
Author

Hi Anil there actually is cause that employee ID which is 1 in the image I attached above is available in both tables.