Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bvssudhakar
Creator III
Creator III

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
Creator
Creator

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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