Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
Can you share those 2 excel files also? we will look into this.
Hi Anil, thanks for the response please see attached two files.
Thanks in advance.
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;
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!!
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?
You may select Sales from Flag filter and check data retrieves correct or not?
Hi Anil unfortunately not, so not sure what might be needed here?
There is no data for that, Could be the reason behind we are not getting data for First and Last Names ??
Hi Anil there actually is cause that employee ID which is 1 in the image I attached above is available in both tables.