topic Re: Canonical Date Linking Problem in QlikView

Canonical Date Linking Problem

bvssudhakar — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Canonical Date Linking Problem

bvssudhakar — Mon, 17 Sep 2018 14:08:12 GMT

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

Please help me out from this guys

Re: Canonical Date Linking Problem

dplr-rn — Mon, 17 Sep 2018 14:57:04 GMT

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

Re: Canonical Date Linking Problem

dplr-rn — Mon, 17 Sep 2018 15:06:58 GMT

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

Re: Canonical Date Linking Problem

vishsaggi — Mon, 17 Sep 2018 15:17:04 GMT

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

Re: Canonical Date Linking Problem

rwunderlich — Mon, 17 Sep 2018 16:03:01 GMT

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

Re: Canonical Date Linking Problem

bvssudhakar — Tue, 18 Sep 2018 07:25:47 GMT

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

Re: Canonical Date Linking Problem

bvssudhakar — Tue, 18 Sep 2018 07:38:59 GMT

Hi Viswarath Nagaraju,

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

Re: Canonical Date Linking Problem

bvssudhakar — Tue, 18 Sep 2018 07:42:44 GMT

Hi Dilip Ranjith,

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

Thank you in advance

Re: Canonical Date Linking Problem

vishsaggi — Tue, 18 Sep 2018 13:53:30 GMT

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

Re: Canonical Date Linking Problem

bvssudhakar — Tue, 18 Sep 2018 13:59:58 GMT

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

Re: Canonical Date Linking Problem

dplr-rn — Tue, 18 Sep 2018 14:03:50 GMT

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

Re: Canonical Date Linking Problem

bvssudhakar — Tue, 18 Sep 2018 14:09:30 GMT

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

Re: Canonical Date Linking Problem

vishsaggi — Tue, 18 Sep 2018 15:32:34 GMT

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