Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT 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?

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)