3 Replies Latest reply: Sep 17, 2018 4:18 AM by Frank Hartmann RSS

    How to show all date columns without synthetic key

    Sudhakar Battula

      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.