Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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');