2 Dates don't tie up

[xarapre7]

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.

[sunny_talwar]

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

[xarapre7]

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.

[sunny_talwar]

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

[xarapre7]

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;