Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bvssudhakar
Contributor

How to show all date columns without synthetic key

Hi Experts,

I have multiple date columns and i created master calendar and fiscal calendar and linked them. But now i want show those date columns also without getting synthetic keys. If i try create those date columns also i am getting synthetic keys to remaining all fields.

here I am mentioning my script:

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

 

 

 

 

Date: 

 

 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,  

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City,  

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

     EnterDate,

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob, 

     'EnterDate' as Date_Flag 

      

Resident MRMqvd; 

 

 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,      

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City,  

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

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

JobEndDate,

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob,   

     'JobEndDate' as Date_Flag 

      

Resident MRMqvd; 

 

 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,  

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City, 

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

     placementdate

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob,   

     'placementdate' as Date_Flag 

      

Resident MRMqvd; 

 

 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,  

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City,  

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

plcdate

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob,   

     'plcdate' as Date_Flag 

 

 

Resident MRMqvd; 

    

 

 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,  

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City,  

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

offerdate 

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob,   

     'offerdate' as Date_Flag 

 

 

Resident MRMqvd; 

    

 

 

LOAD jobid,  

     TitleName,  

     ishotjob,  

     NoOpenings,  

     JobClassid,  

     Location,  

     Department,  

     CandidateID,  

     City, 

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

DeclineDate

     PeopleSourceName,  

     Recruiter,  

     CandidateName,  

     skillName,

     StatusofJob,   

     'DeclineDate' as Date_Flag 

 

 

Resident MRMqvd; 

 

 

DROP Table MRMqvd;

Master & Fiscal Calendar Script:

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;

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

Now my requirement is :

One straight table with these fields :TitleName, Department, Jobid, EnterDate, PlacementDate, count of days between EnterDate & PlacementDate

How can i create

Can you gus please help me out from this

For your reference here i am attaching qvw. But whatever the solution please post here also because i am using personal edition

Thank you in advance.

3 Replies
dilipranjith
Valued Contributor

Re: How to show all date columns without synthetic key

not clear what you mean by "show those date columns also without getting synthetic keys"

Do you mean create master calendar for both date fields?

if so you may need to use canonical dates

Canonical Date

bvssudhakar
Contributor

Re: How to show all date columns without synthetic key

Hi Dilip Ranjith,

I have tried with Canonical Date but i am getting this error while creating the master calendar for Canonical Date

Capture.JPG

Why i am getting this error. Can you please explain me

For your reference here i am attaching my qvw also Please check it and help me out from this Thank you in advance

franky_h79
Honored Contributor

Re: How to show all date columns without synthetic key