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]

Hi Vishwarth Nagaraju,

I Found Some other solution for my requirement at present this not required. But anyway thank you for your support

If possible can you please check for this and give the possible solution

How to create hierarchy calendar from master calendar

Thank you in advance

[dplr-rn]

i can try at my end as i dont have your qvds.

2 approaches you may need to pickone depending on business scenario

1) if you it makes business sense to eliminate the older entry for candidates (see old screenshot leena )

eliminate the duplicate candidate ids .

2) if you need to retain older ones - as Rob sugggested dont load as mapping load . load it as regular table (with not null) and concatenate jobendcalendar, plccalendar etc. this maybe the better approach

[bvssudhakar]

Hi Dilip Ranjith & vishsaggi‌

Here, i am sharing my qvd, whenever you people have time please create it new file with that qvd. So that it will useful for me in near future.

And I am very curious to know about this thing

Thank you in advance

[vishsaggi]

You qvd has dates that are showing 1900 year for plcdate and Jobend date. Are they correct if not can Today()'s date can be those dates. May be try below as i have changed 1900 year dates to todays date so every day those two fields will have todays date:

MRMqvd:

LOAD jobid,

     TitleName,

     ishotjob,

     NoOpenings,

     EnterDate,

     JobEndDate,

     JobClassid,

     Location,

     Department,

     CandidateID,

     City,

     placementdate,

     plcdate,

     PeopleSourceName,

     Recruiter,

     CandidateName,

     skillName,

     offerdate,

     DeclineDate,

     StatusofJob

FROM

(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(IF(Year(JobEndDate) = '1900',Today(), 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(IF(Year(plcdate) = '1900',Today(), Left(plcdate,10)), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,

     //Date#(Date(Today(), '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;

-----------------------------------------------------------------------------------------

//Master Calendar revised

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

          *;

MasterCalendar:

Load

TempDate AS LinkDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('LinkDate', recno()))-1 as mindate,

max(FieldValue('LinkDate', recno())) as maxdate

AUTOGENERATE FieldValueCount('LinkDate');