Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
bvssudhakar
Contributor II

How to link master calendar dates to our dates in data

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

3 Replies
rajivmeher
Contributor

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

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.

bvssudhakar
Contributor II

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

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

bvssudhakar
Contributor II

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

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