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.
