Canonical Date Linking Problem

[bvssudhakar]

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.

[bvssudhakar]

mbaeyens‌stalwar1‌petter-s‌manoj217‌lfholland‌hic‌loveisfail‌@Rob

Please help me out from this guys

[dplr-rn]

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

[dplr-rn]

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

[vishsaggi]

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

[rwunderlich]

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]

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]

Hi Viswarath Nagaraju,

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

[bvssudhakar]

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]

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