topic Re: Linking two calendars into a master calendar in QlikView

Linking two calendars into a master calendar

kaygee28 — Sat, 05 May 2018 04:36:17 GMT

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?

Re: Linking two calendars into a master calendar

Anil_Babu_Samineni — Sat, 05 May 2018 06:11:18 GMT

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

Re: Linking two calendars into a master calendar

kaygee28 — Sat, 05 May 2018 12:35:43 GMT

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

Thanks in advance.

Re: Linking two calendars into a master calendar

Anil_Babu_Samineni — Sat, 05 May 2018 13:20:18 GMT

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;

Re: Linking two calendars into a master calendar

kaygee28 — Sat, 05 May 2018 14:01:03 GMT

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!!

Re: Linking two calendars into a master calendar

kaygee28 — Sat, 05 May 2018 17:31:20 GMT

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?

Re: Linking two calendars into a master calendar

Anil_Babu_Samineni — Sun, 06 May 2018 09:48:09 GMT

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

Re: Linking two calendars into a master calendar

kaygee28 — Sun, 06 May 2018 12:25:45 GMT

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

Re: Linking two calendars into a master calendar

Anil_Babu_Samineni — Mon, 07 May 2018 15:48:07 GMT

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

Re: Linking two calendars into a master calendar

kaygee28 — Mon, 07 May 2018 17:12:56 GMT

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

Re: Linking two calendars into a master calendar

Anil_Babu_Samineni — Tue, 08 May 2018 00:33:58 GMT

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;

Re: Linking two calendars into a master calendar

kaygee28 — Tue, 08 May 2018 07:08:53 GMT

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.

Re: Linking two calendars into a master calendar

Anil_Babu_Samineni — Wed, 09 May 2018 00:35:38 GMT

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