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');

[Peter_Cammaert]

Use the Table Viewer to check the content of field ¨%TimeStamp. QlikView probably doesn't recognise your timestamp field and will store NULL values in minTimeStamp and maxTimeStamp. You can check the content of table TempMinMaxTime as well.

Peter

[andreas_koehler]

Peter,

%TimeStamp has 100% density in TValues.  The table TempMinMaxTime has 1 row with no content.

Thanks for your quick reply.

[HirisH_V7]

Hi,

Can you post your time stamp data in Excel.

[andreas_koehler]

Hirish,

I read a qvd-file. The original xlsx is very long, The original timestamp has the format DD.MM.YYYY hh:mm, the same as my

SET DateFormat='DD.MM.YYYY hh:mm';

[Peter_Cammaert]

Alignment-wise, your %TimeStamp field appears to contain text strings (left-aligned) , while Min() and Max() require numerical values. That's the reason why TempMinMaxTime contains NULL values in both columns.

To force the use of the variable DateFormat to format column Date into a dual value, you should use Date#() instead of Timestamp#(). Or you can leave the Timestamp#() call, but in order to make it work, use

SET TimestampFormat = 'DD.MM.YYYY hh:mm';

[MarcoWedel]

Hallo Andreas,

please post some line of your Excel file to demonstrate.

thanks

regards

Marco

[HirisH_V7]

Post some sample records too look into ..

[jagan]

Hi,

Try changing this portion

TempMinMaxTime:

Load

Min(%TimeStamp) as minTimeStamp,

Max(%TimeStamp) as maxTimeStamp

resident [TValues];

Regards,

Jagan.

[marcus_sommer]

Beside the suggestions from Peter to convert your field properly into a numeric one should be the thoughts here helpful: Re: Qlik Sense - Duplicate timepoints‌.

- Marcus