Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bvssudhakar
Creator III
Creator III

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
Creator III
Creator III
Author

mbaeyensstalwar1petter-smanoj217lfhollandhicloveisfail‌@Rob

Please help me out from this guys

dplr-rn
Partner - Master III
Partner - Master III

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

dplr-rn
Partner - Master III
Partner - Master III

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
Champion III
Champion III

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hi Viswarath Nagaraju,

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

bvssudhakar
Creator III
Creator III
Author

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
Champion III
Champion III

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