How to show all date columns without synthetic key

bvssudhakar — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to show all date columns without synthetic key

dplr-rn — Fri, 14 Sep 2018 15:55:01 GMT

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

Re: How to show all date columns without synthetic key

bvssudhakar — Mon, 17 Sep 2018 07:08:47 GMT

Hi Dilip Ranjith,

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

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

Re: How to show all date columns without synthetic key

Frank_Hartmann — Mon, 17 Sep 2018 08:18:37 GMT

see here:

Master Calendar "Field not found" Error

topic Re: How to show all date columns without synthetic key in QlikView

How to show all date columns without synthetic key

Re: How to show all date columns without synthetic key

Re: How to show all date columns without synthetic key

Re: How to show all date columns without synthetic key