counting occurences per day and putting them per day

[Cathalc]

Hi all!

I've been working on this problem for a while:

I'm creating a mastercalendar for a table which contains the occurence of defects per time and date stamp. E.g:
01/01/2019 15:15:36    a
01/01/2019 19:15:36    a
01/02/2019 01:15:36    a
01/03/2019 15:15:36    a
01/03/2019 23:15:36    a

Since my other master calendars don't have timestamps, I want to count the number of occurrences of a  per day and put this number in a dateformat (no timestamp).

Does anyone have an idea how to do this? I've found many methods to convert dtime to datestamps but I'm not sure if the occurences are then counted? Or will I get multiple lines with the same date?

The end result should look like this:

01/01/2019    2
01/02/2019    1
01/03/2019    2

Thanks and have a nice day!

[JustinDallas]

Hello Cathartic,

Generally, when you have a Master Calendar, you want it to be grouped by days and not timestamps.  I learned that lesson the hard way.  Here is how I would structure my MasterCalendar if given the sample information that you have listed:

LetterTimestamp:
LOAD *,
DayStart(Floor(Num(TimeStamp))) AS '%cal_date_key'
;
LOAD *,
Timestamp(Timestamp#(TimetstampText, 'MM/DD/YYYY hh:mm:ss')) AS 'TimeStamp'
;
LOAD * Inline
[
'TimetstampText', 'LetterString'
'01/01/2019 15:15:36',    'a'
'01/01/2019 19:15:36',    'a'
'01/02/2019 01:15:36',    'a'
'01/03/2019 15:15:36',    'a'
'01/03/2019 23:15:36',    'a'
]
;
DROP FIELD TimetstampText
;

MasterCalendar: 
Load 
 TempDate AS '%cal_date_key', 
 DayStart(TempDate) as CalDate,
 Week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Month(TempDate) & '-' & Year(TempDate) as MonthYear, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('%cal_date_key', recno()))-1 as mindate,
 max(FieldValue('%cal_date_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%cal_date_key');

EXIT Script
;

[Cathalc]

Hi JustinDallas,

Thank you for the reply! This helps a lot. I tried adapting my code to yours but when i look at the table i generated I see that the %cal_date_key s are all empty.

This i what I got now:  Any ideas?

LetterTimestamp:
LOAD *,
DayStart(Floor(Num(TimeStamp))) AS '%cal_date_key'
;
LOAD *,
Timestamp(Timestamp#(TimetstampText, 'MM/DD/YYYY hh:mm:ss')) AS 'TimeStamp'
;

LIB CONNECT TO 'RALNHV (nhv_qlikadmin)';
FactPIREPMAREP:
LOAD *;
SQL select 
    tATA.ATA AS ATA,
    tATA.ATAChapter AS ATAChapter,
    tATA.ATASystem AS ATASystem,
    tATA.ATALocal AS ATALocal,
    tATA.Description AS ATADescription,
    RALBaseReportedFrom,
    CreatedDate as TimetstampText,
    TaskText,
    CarriedOutText,
    sOrderTask.Description AS Description,
    tReg.Reg AS RegistrationName,
    OrderTaskType
from sOrderTask 
left JOIN sOrderTaskType on sOrderTaskType_ID = sOrderTaskType.ID
left join tATA on tATA.ID = tATA_ID
left join sOrder on sOrder_ID = sOrder.ID
left join tReg on tReg_ID = tReg.ID
where sOrderTaskType_ID = 2
or sOrderTaskType_ID = 3;


DROP FIELD TimetstampText
;

MasterCalendar: 
Load 
 TempDate AS '%cal_date_key', 
 DayStart(TempDate) as CalDate,
 Week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Month(TempDate) & '-' & Year(TempDate) as MonthYear, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('%cal_date_key', recno()))-1 as mindate,
 max(FieldValue('%cal_date_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%cal_date_key');

EXIT Script
;