topic Re: How to join two dates to calendar date? in QlikView

How to join two dates to calendar date?

Tue, 14 Oct 2014 12:17:22 GMT

Hi,

I have two columns DateEntered and DateProcessed. I need to join these two dates to calendar date. How can I do this in script. Please see below as I have this in tables

TblTemp:
LOAD
     ID,


     Date(DateEntered,'DD/MM/YYYY')                                             as DateEntered,

     Date(DateProcessed,'DD/MM/YYYY')                                           as DateProcessed

FROM
[Table.qvd]
(qvd);

TblTemp1:
LOAD *,
IF(ID = PREVIOUS (ID),1,0) as Flag1
RESIDENT
TblTemp
ORDER BY ID ASC,DateEntered ASC;

Tbl:
LOAD *,
IF(ID = PREVIOUS (ID) AND Order <> Previous(Order) , DateEntered-PREVIOUS(DateEntered1),' ') as CycleTime
RESIDENT
TblTemp1;

DROP TABLE TblTemp;
DROP TABLE TblTemp1;

LEFT JOIN (Tbl)

LOAD
ID,
MAX(DateEntered) as MaxDateEntered

RESIDENT
Tbl
GROUP BY ID
ORDER BY ID,DateEntered ;

LEFT JOIN (Tbl)

LOAD
ID,
EnteredBy as Member
RESIDENT TblIssue
WHERE DateDateEntered1 = MaxDateEntered;

Calendar:

LOAD Date

     Date_TS,
     Today,
     Week,
     Year,
     Text(Month)                     as Month,
     ApplyMap('SortMap',Text(Month)) as MonthSort,
     Day,
     WeekDay,
     Quarter,
     MonthYear,
     WeekYear,
     CurYTDFlag,
     LastYTDFlag,
     FiscalYear,
     FiscalYearStart,
     FiscalWeek,
     FiscalWeekSerial,
     FiscalQuarter

FROM
[Calendar.qvd]
(qvd);

Please can anyone suggest me as I tried to get this but not seems using correct.

Re: How to join two dates to calendar date?

alexandros17 — Tue, 14 Oct 2014 12:21:33 GMT

You need 2 calendars one for each date ...

Re: How to join two dates to calendar date?

Gysbert_Wassenaar — Tue, 14 Oct 2014 12:22:53 GMT

This blog post may help: Why You sometimes should Load a Master Table several times

Re: How to join two dates to calendar date?

maleksafa — Tue, 14 Oct 2014 12:28:18 GMT

check for canonical dates.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: How to join two dates to calendar date?

Tue, 14 Oct 2014 12:49:44 GMT

Thanks. I tried two calendars but it is not working. How to get this by joining above script? Please help

Re: How to join two dates to calendar date?

Wed, 15 Oct 2014 08:17:32 GMT

and also when I am creating two calendars it is getting synthetic keys. Please help

Re: How to join two dates to calendar date?

Wed, 15 Oct 2014 08:18:19 GMT

Thanks. I tried this but seems not getting to use with the data which I have. Can please help me.

Re: How to join two dates to calendar date?

Wed, 15 Oct 2014 12:18:07 GMT

Thanks. I confused earlier. But this working exactly.