topic Re: How to link master calendar dates to our dates in data in QlikView

How to link master calendar dates to our dates in data

bvssudhakar — Wed, 05 Sep 2018 07:58:56 GMT

Hi Experts,

I have a qvd with multiple dates for that i made these changes in script to set as one date column then i have created master calendar. Now i want to create link with those two dates.

Can you guys please help me out from this. Here i am posting what i made changes for my qvd in script and master calendar script also

MRMqvd:-------------This is my qvd file

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

EnterDate,

JobEndDate,

JobClassid,

Location,

Department,

CandidateID,

City,

placementdate,

plcdate,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

offerdate,

DeclineDate

FROM

MRM.QVD

(qvd);

Date:---- This one i did

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

Date(EnterDate, 'DD/MM/YYYY') as LinkDate,

JobClassid,

Location,

Department,

CandidateID,

City,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

EnterDate as Date_Flag

Resident MRMqvd;

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

Date(JobEndDate, 'DD/MM/YYYY') as LinkDate,

JobClassid,

Location,

Department,

CandidateID,

City,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

JobEndDate as Date_Flag

Resident MRMqvd;

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

JobClassid,

Location,

Department,

CandidateID,

City,

Date(placementdate, 'DD/MM/YYYY') as LinkDate,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

placementdate as Date_Flag

Resident MRMqvd;

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

JobClassid,

Location,

Department,

CandidateID,

City,

Date(plcdate, 'DD/MM/YYYY') as LinkDate,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

plcdate as Date_Flag

Resident MRMqvd;

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

JobClassid,

Location,

Department,

CandidateID,

City,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

Date(offerdate, 'DD/MM/YYYY') as LinkDate,

offerdate as Date_Flag

Resident MRMqvd;

LOAD jobid,

TitleName,

ishotjob,

NoOpenings,

JobClassid,

Location,

Department,

CandidateID,

City,

PeopleSourceName,

Recruiter,

CandidateName,

skillName,

Date(DeclineDate, 'DD/MM/YYYY') as LinkDate,

DeclineDate as Date_Flag

Resident MRMqvd;

DROP Table MRMqvd;

Then i have created a master calendar But now i am not able to do linking between these two date columns:

Temp:

LOAD

Max(LinkDate) as MaxDate,

Min(LinkDate) as MinDate

Resident Date;

let vMaxDate = Floor(YearEnd(Peek('MaxDate',-1,'Temp')));

let vMinDate = Floor(YearStart(Peek('MinDate',-1,'Temp')));

drop table Temp;

Calendar:

Load

Date(DateNum) as Date,

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

Please Help me out from this

Thank you in advance

Regards,

B V S Sudhakar

Re: How to link master calendar dates to our dates in data

rajivmeher — Wed, 05 Sep 2018 11:18:37 GMT

Hi bvssudhakar‌

Please trying changing

Load

Date(DateNum) as Date,

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;

To:

Load

Date(DateNum, 'DD/MM/YYYY') as LinkDate,

Date(DateNum) as Date,

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;

This will help the formatting on both tables to be same for data and common column name will join the tables automatically on reload.

Regards

Rajiv.

Re: How to link master calendar dates to our dates in data

bvssudhakar — Wed, 05 Sep 2018 11:31:17 GMT

Hi Rajiv,

Thank you for your answer.

Actually I went on wrong way for my requirement. Just now only i realized. And I have opened new discussion can you please see this and help me out from this:

Link with Master Calendar

Thank you in advance

Re: How to link master calendar dates to our dates in data

bvssudhakar — Wed, 05 Sep 2018 11:43:31 GMT

Hi Rajiv,

Thank you for your answer.

Actually I went on wrong way for my requirement. Just now only i realized. And I have opened new discussion can you please see this and help me out from this:

Link with Master Calendar

Other wise i want to create text object like this expression:

Count(CandidateID) but i want show for only plcdate column dates candidateID's Count only.

Means If i create the 2018 year and april month from master date calendar list boxes output should be 15.

Is it possible with this script. Can you please check for that

Thank you in advance