Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

kaygee28
New 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

Re: Linking two calendars into a master calendar

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;

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

Re: Linking two calendars into a master calendar

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

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

Re: Linking two calendars into a master calendar

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

Thanks in advance.

Re: Linking two calendars into a master calendar

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;

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

kaygee28
New Contributor III

Re: Linking two calendars into a master calendar

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

Re: Linking two calendars into a master calendar

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

Re: Linking two calendars into a master calendar

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

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)
Highlighted
kaygee28
New Contributor III

Re: Linking two calendars into a master calendar

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

sales2.PNG

Re: Linking two calendars into a master calendar

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

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

Re: Linking two calendars into a master calendar

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