Master Calendar is not in the correct format as in sql script

[Evanpap]

HI all,

I have a problem that I am struggling with:

I have an sql script where I try to retrieve the date like this: to_timestamp(s.eventdate, 'MM/DD/YYYY HH12:MI:SS.MS AM') as eng_dt.

Then I try to create a Master calendar like below:

[Master Calendar]:

Load
Date([TempDate],'MM/DD/YYYY HH12:MI:SS.MS AM') as eng_dt,
day(TempDate) as day,
week(TempDate) as week,
Month(TempDate) as month,
//‘Q’ &Ceil(Month(TempDate) / 3) AS Quarter,
year(TempDate) as year
;

// ===Generate a temp table of dates===
Load
date(mindate + IterNo()) as TempDate
, maxdate //Used in InYearToDate(), but not kept
While mindate + IterNo() <=maxdate
;

//=== Get min/max dates from Field===/
Load
min(FieldValue('eng_dt', recno()))-1 as mindate,
max(FieldValue('eng_dt', recno())) as maxdate
autogenerate Fieldvaluecount ('eng_dt');

After I load the data and I check my table for the calendar I have the date format like below in this strange format and I don't know why. And hence I have problem joining the tables.

Format in the Master Calendar 08/01/2021 1212:8I:49.849 A8

Can you please help me and suggest the correct way of joining the data?

Thank you very much

Evan

[Or]

Those are not valid Qlik time stamp formats. In particular, I see three mistakes: the 12 isn't needed, 'mm' and not 'MI', and 'TT' rather than 'AM'.

[Evanpap]

Thank you for the help!

I updated the Master Calendar and looks like this:

[Master Calendar]:

Load
Date([TempDate],'MM/DD/YYYY HH:MM:SS TT') as eng_dt,
day(TempDate) as day,
week(TempDate) as week,
Month(TempDate) as month,
//‘Q’ &Ceil(Month(TempDate) / 3) AS Quarter,
year(TempDate) as year
;

// ===Generate a temp table of dates===
Load
date(mindate + IterNo()) as TempDate
, maxdate //Used in InYearToDate(), but not kept
While mindate + IterNo() <=maxdate
;

//=== Get min/max dates from Field===/
Load
min(FieldValue('eng_dt', recno()))-1 as mindate,
max(FieldValue('eng_dt', recno())) as maxdate
autogenerate Fieldvaluecount ('eng_dt');

However I see the following in the data. Looks like the values for month etc are not being mapped correctly..

[Or · Accepted solution]

Those are still not quite right - you should be using lowercase mm for minutes  (MM is months) and generally hh:mm:ss for the timestamp section (I don't think the other two matter but it's easier to read).

As for the rest of it, it's pretty clear those values aren't getting formatted (due to the lack of leading 0s), so I'd expect that either they're not being parsed as dates or they're coming in from another source.

[marcus_sommer]

Beside of your converting and formatting issues - a "normal" master calendar doesn't contain times. Therefore you may cut the date-part respectively if it's correctly interpreted as timestamp you may use: date(floor(YourField)) to get a real date.

If also times are important within your data-model you could add a master timetable which is linked to the times and only by very specific use-cases it makes sense to create an extra table with a mix of dates + times.

[Evanpap]

Yes it seems that the source, SQL is not really helping me with the format. I need to find another way then than do this.

Anyway, thanks for the help!