Calendar with hourly data that matches imported hourly data needed

[andreas_koehler]

Dear all,

I need to analyse hourly values that are imported from excel in one field with the format "DD.MM.YY mm:ss". This is the key field [%Date] with a calendar I need for set analysis.

I found here several routes to create a calendar with a hourly /or even smaller granularity such as Master Calendar with every 30 Minutes

With the variations I used I could not figure out how to consistently match the original data with the data in Qlikview.

To show the issues I use a version derived from the Master calendar script from the bood 'Qlikview 11 for Delopers', adapted for hours.

I also found a reference to Rob Wunderlich's Correct Time Arithmetic | Qlikview Cookbook , that deals with differen underlying numeric value, but I could not figure out how to translate this into the calendar.

Can someone help?

Here is a table exported from Qlikview (ImportedDate is the original Date, CalendarDate the Date created with the code below and #Data the data associated to the ImportedDate.Cleary data would get lost in set analysis.

Code:

[TValues]:

LOAD Date as %Date,

    Date as ImportedDate,

     TagCode as %TagCode,

     #Data

FROM

[$(vTransformed)TFacts.qvd]

(qvd);

LET vMinDate = Num(MakeDate(2013,1,1));  // Calendar Start Date

LET vMaxDate = Num(Today());

[Master Calendar]:

Load Distinct

    TempDate                                     as [%Date],

    TempDate                                    as [CalendarDate],

    Year([TempDate])                             as Year,

    Month([TempDate])                             as Month,

    'Q' & Ceil(Month([TempDate])/3)                as Quarter,

    Week([TempDate])                             as Week,

    Day([TempDate])                                as Day,

    Hour([TempDate])                             as Hour,

    Year([TempDate])*10000+Month([TempDate])*100+Day([TempDate])        as PeriodDays;

Load Distinct

    $(vMinDate)+(IterNo()-1)/24   as TempDate

    AutoGenerate (1)

While (($(vMinDate) + (IterNo()-1)/24) <= $(vMaxDate));

[swuehl]

It's kind of strange that this code

LOAD Date as %Date,

    Date as ImportedDate,

     TagCode as %TagCode,

     #Data

FROM

[$(vTransformed)TFacts.qvd]

(qvd);

creates two fields with two different timestamp formats 'DD.MM.YYYY hh:mm' vs 'DD.MM.YYYY hh:mm:ss'.

Also I can't see how you applied the format for your master calendar timestamps.

Or have you manipulated the format in the table box object?

In general, I think Rob's blog post explains the issue quite well, what have you tried already in your master calendar?

Instead of a floating point value, you can also create a key by using an integer, like

LOAD Floor(Date)*100 + Hour(Date) as %Date,

     .....

[andreas_koehler]

Swuehl,

Thanks for your reply.

Yes, I changed the format in the table box. I added ImportedDate and CalendarDate just for clarfication in this post.

My settings are

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

Concerning your proposal to use Floor(Date)*100 + Hour(Date) as %Date:

I am using %Date to get the minDate and MaxDate for the calendar via the following Load statement:

Load

Num(Min(%Date),'DD.MM.YYYY hh:mm')                as minDate,

Num(Max(%Date),'DD.MM.YYYY hh:mm')                as maxDate

resident [TValues];

I would believe that this makes your proposed solution rather complex.

Concerning Rob's blog post I took away that this can be resolved using time#(time() in boths tables for the key field. It ended in a disaster. So I figured out that I need some more guidance how to proceed. Can you provide me some on how to use Rob's advice in this context?

Besides: Just to understand how this board works: Were did the "right answer" badge come from?

[swuehl · Accepted solution]

Time#(Time(FIELD)) should work, but only if FIELD has a numeric representation (so it can be formatted by Time() function)

See also Why don’t my dates work?

I agree that matching floating point values can be difficult, so I prefer to use integer / strings for keys.

P.S. Regarding Correct / Helpful answers: Re: How the points earn in Qlik.

[Peter_Cammaert]

Someone is messing with this discussion, as your second post disappeared without a trace while I was entering a reply to "where does the right answer badge come from". So here goes:

You mean, the "Correct Answer" marker that hovers over every reply in a discussion you created yourself? That one allows you to mark any reply as the correct answer to your question (and as a result assign points to the poster). Those green buttons stay available afterwards, so that you can correct the assignment if you made an error or got a better, more appropriate or more precise answer.

You can mark a reply as Helpful as well. The button to do so hides in the Actions menu at the bottom left of every post. There are always zero or one Correct Answers. But you can assign as many Helpfuls as you want.

Good luck,

Peter

[andreas_koehler]

swuehl,

thanks for your answer and the link. This article has helped me a lot to understand the specifics in working with dates.

I resolved my issue by applying Timestamp#() to use the numerical value to link the calendar and the fact table.