topic Re: 2 Dates don't tie up in QlikView

2 Dates don't tie up

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

Hi Experts!

I have 2 dates (StartDate and FullDate) which do not tie up. I've used Date(Date#(Left(StartDate, 8), 'YYYYMMDD'), 'YYYY-MM-DD')

to convert StartDate to date and match the FullDate's format. Originally,it was in decimal. Now, when I'm selecting

any date from StartDate, no date is coming up from the FullDate table and everything is just grey.

Can you please help? Thank you in advance.

Re: 2 Dates don't tie up

sunny_talwar — Wed, 24 Jan 2018 19:00:19 GMT

Are they coming from different tables? have you made sure that there is a connection between the two tables?

Re: 2 Dates don't tie up

xarapre7 — Wed, 24 Jan 2018 19:08:21 GMT

Yes, they are coming from different tables.

I'm using a link table and alias the StartDate as 'FullDate' to connect the 2 tables but still not tying up.

Re: 2 Dates don't tie up

sunny_talwar — Wed, 24 Jan 2018 19:11:14 GMT

Can you share an image of your data model and also share the script?

Re: 2 Dates don't tie up

xarapre7 — Wed, 24 Jan 2018 19:48:30 GMT

DATE:

LOAD

FULL_DATE AS 'CREATE_FULL_DATE',

MONTH,

MONTH_ABBREV as 'Month',

QUARTER as 'Quarter',

YEAR as 'Year'

FROM

$(vQVDPath) (qvd)

;

DETAILS:

LOAD

IF(ISNULL([EmployeeID]),'9999999999',[EmployeeID])&'|'&Date#(Date([CallDate],'YYYY-MM-DD'),'YYYY-MM-DD') as %KeyField,

IF(ISNULL([EmployeeID]),'9999999999',[EmployeeID]) AS 'DetailsEmployeeID'

,[CallDate]

FROM

$(vQVDPath) (qvd)

;

SRInteract:

LOAD

if(isnull([ResponsiblePerson]) or len(trim([ResponsiblePerson])) = 0 ,'<N/A>',replace(ltrim(replace([ResponsiblePerson],'0',' ')),' ','0'))&'|'&Date(Date#(Left(StartDate, 8), 'YYYYMMDD'), 'YYYY-MM-DD') as %INTKeyField,

,Date(Date#(Left(StartDate, 8), 'YYYYMMDD'), 'YYYY-MM-DD') as 'StartDate'

, IF(ISNULL([ResponsiblePerson]),'9999999999',[ResponsiblePerson]) as 'ResponsiblePerson'

FROM

$(vQVDPath2) (qvd)

;

LINK_TABLE:

LOAD Distinct

%KeyField

,%InteractionKeyField

,Date#(Date([CallDate],'YYYY-MM-DD'),'YYYY-MM-DD') AS FULL_DATE

,[DetailsEmployeeID] as EMPID

, CustomerID

Resident DETAILS

;

CONCATENATE(LINK_TABLE)

LOAD Distinct

%INTKeyField

,%INT_InteractKeyField

,Date#(Date(Left(INTBeginDate, 8), 'YYYYMMDD'), 'YYYY-MM-DD') AS FULL_DATE

,INTResponsiblePerson as 'EMPID'

Resident SRInteract

;

drop field DetailsEmployeeID, CallDate, CustomerID,%KeyField FROM DETAILS;

drop FIELD FULL_DATE, InteractionID, %INTKeyField from SRInteract;