Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
bvssudhakar
Contributor II

Canonical Date Linking Problem

Hi Experts,

I am facing problem with Canonical Date Linking. I have a table with multiple date columns. So i have create canonical date calendar. but i am getting wrong values and also i have noticed some connecting problem also

E.g: I have created one text object with the following expression

=Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},DateType={'plcdate'}>}CandidateID)

And also created plcYear list box and Master Calendar's Year and Month list boxes

When i Select the year 2018 from plc Year i got correct value :72, But That is not the way i want to show only one master calendar year list box. If i select the year from Master Calendar's Year list box it is giving wrong value.


I have noticed one thing i have selected 2018 year from plc Year list box Master Calendar listbox Jan month is going to grey colour that means data is not linked correctly.


Here I am posting Screen shots of those and as well as i am sharing my qvw also


Please help me out from this.



Capture1.JPG


Capture.JPG

13 Replies
bvssudhakar
Contributor II

Re: Canonical Date Linking Problem

mbaeyensstalwar1petter-smanoj217lfhollandhicloveisfail‌@Rob

Please help me out from this guys

Partner
Partner

Re: Canonical Date Linking Problem

Hi Sudhakar

you need to review the canonical load script again. the linking is not right

i created a straight table to diagnose and there seems to be incorrect years associated with them. for plcdate 2018

blank and 2015 (canonical year) is associated

See screenshot attached

Capture.PNG

Partner
Partner

Re: Canonical Date Linking Problem

analyzing it further. the reason is there are older values for some candidates with same candidate id

see below 1 example. Just load the map (e.g. plcCalendar) with not null condition

Capture.PNG

vishsaggi
Esteemed Contributor III

Re: Canonical Date Linking Problem

Try this in your script and check. Using canonical date link table. Just copy the below script and reload and check your data model. It should have one fact table| Once Calendar Table| and one DateLink table joined on Date field and jobid field.

MRMqvd: 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,  

     EnterDate, 

     Date(Date#(EnterDate, 'DD-MMM-YYYY')) as Date, 

     JobEndDate, 

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City,  

     placementdate,  

     plcdate,  

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob,  

     offerdate,  

     DeclineDate 

FROM 

MRM.QVD 

(qvd); 

 

 

 

DateLink:

LOAD jobid,

     Date#(Date(left(EnterDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(left(EnterDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as EnterDate,

     'EnterDate' as Date_Flag 

      

Resident MRMqvd; 

  

LOAD

     jobid,

     Date#(Date(left(JobEndDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(left(JobEndDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as JobEndDate,

     'JobEndDate' as Date_Flag 

      

Resident MRMqvd; 

 

LOAD jobid,

     Date#(Date(left(placementdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(left(placementdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as placementdate,

     'placementdate' as Date_Flag 

      

Resident MRMqvd; 

 

  

LOAD jobid,

     Date#(Date(left(plcdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(left(plcdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as plcdate,

     'plcdate' as Date_Flag 

   

Resident MRMqvd; 

 

   

LOAD jobid,

     Date#(Date(left(offerdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(left(offerdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as offerdate, 

     'offerdate' as Date_Flag 

 

 

Resident MRMqvd; 

    

  

LOAD jobid,

     Date#(Date(left(DeclineDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(left(DeclineDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as DeclineDate,

     'DeclineDate' as Date_Flag 

 

 

Resident MRMqvd; 

Temp:

LOAD

    Max(Date) as MaxDate,

    Min(Date) as MinDate

Resident MRMQvd;

let vMaxDate = Floor(YearEnd(Peek('MaxDate',-1,'Temp')));

let vMinDate = Floor(YearStart(Peek('MinDate',-1,'Temp')));

drop table Temp;

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:

                                                     

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

Load

  Date(DateNum, 'MM/DD/YYYY') as LinkDate,

  Day(DateNum) as Day,

  Month(DateNum) as Month,

  Week(DateNum) as Week,

  WeekDay(DateNum)as WeekDay,

  'Q' & Ceil(Month(DateNum)/3) as Quarter,

  Year(DateNum) as Year;

Load

  $(vMinDate) + IterNo() - 1 as DateNum

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

MVP & Luminary
MVP & Luminary

Re: Canonical Date Linking Problem

Why are you using mapping to create the DateBridge table?  It seems to me that would give incorrect results if a CandidateId ever appeared more than once in a table.

-Rob

bvssudhakar
Contributor II

Re: Canonical Date Linking Problem

Hi Rob,

I don't have much idea about this Date Bridge and Canonical Date, Please suggest some good solution to my requirement

Thank you in advance

bvssudhakar
Contributor II

Re: Canonical Date Linking Problem

Hi Viswarath Nagaraju,

I tried with this script, I am getting errors for Master calendar, Can you please check for this once

bvssudhakar
Contributor II

Re: Canonical Date Linking Problem

Hi Dilip Ranjith,

Thank you for your analyzation, Can you please try once in new file and share with me

Thank you in advance

vishsaggi
Esteemed Contributor III

Re: Canonical Date Linking Problem

Can you tell me the error, coz i cannot check at my end as i do not have your qvd file.