Calendar: Struggling with min- and max-Dates

[andreas_koehler]

Hi !

I want to use timestamps of a time series to generate Min and Max timestamps for a calendar. I use timestamp#(Date) as I read hourly data.

Problem is that the following lines do not generate values for the variables as intented:

vTMinDate = <NULL> and vTMaxDate = <NULL>.

Why is this so?

[TValues]:

LOAD Timestamp#(Date)                                    as %TimeStamp,

         TCode                                                        as %TCode,

     #Data

FROM

[$(vTransformed)PFacts.qvd]

(qvd);

TempMinMaxTime:

Load

Num(Min(%TimeStamp))                            as minTimeStamp,

Num(Max(%TimeStamp))                            as maxTimeStamp

resident [TValues];

let vTMinDate = Peek('minTimeStamp',0,'TempMinMaxTime');

let vTMaxDate = Peek('maxTimeStamp',0,'TempMinMaxTime');

[marcus_sommer]

These excel contained not your crosstable-data and is therefore ot very helpful (in my case will be these date automatically as numeric interpreted).

- Marcus

[andreas_koehler]

Thanks Max,

it works.First time I got values for my variables and it was minimally invasive (which is good for a novice like me to keep track with the changes). I also had to change the load statement in the Master calendar as it first did not match.

What worked:

[TValues]:

LOAD

     Timestamp#(Date)                                    as %TimeStamp,

     TCode                                                        as %TCode,

     #Data

FROM

[$(vTransformed)PFacts.qvd]

(qvd);

[TempMinMaxTime]:

Load

Min(%TimeStamp)                                as minTimeStamp,

Max(%TimeStamp)                                as maxTimeStamp

resident [TValues];

let vMinDate = Peek('minTimeStamp',0,'TempMinMaxTime');

let vMaxDate = Ceil(Peek('maxTimeStamp',0,'TempMinMaxTime'));

[Master Calendar]:

Load Distinct

    Timestamp(Num#(TempDate))                     as %TimeStamp,

     etc..

Now I try to figure out why the other versions did not. I used QVfD and HC's technical brief on QlikView date fields to understand the concepts but seems that I have to dig deeper into this..

Thanks to all who shared their knowledge with me!