Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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?

12 Replies
Anil_Babu_Samineni

Can you check this?

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

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;

LinkTable:

LOAD AutoNumber([Employee ID] & '|' & DateField & '|' & Flag) as Key, [Employee ID], DateField, Flag Resident Employee;

Concatenate(LinkTable)

LOAD AutoNumber([Employee ID] & '|' & DateField & '|' & Flag) as Key,[Employee ID], DateField, Flag Resident Sales;

DROP Fields [Employee ID], DateField, Flag From 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)
kaygee28
Contributor III
Contributor III
Author

Oh I see what you did, I'm certainly learning a lot from here, this is straight genius from you Anil I don't even know what to say I'm so ecstatic thank you so much!!

I understand completely what you did here.

Anil_Babu_Samineni

No Problem at all, Please mark some helpful which met your requirement ...

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)