Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andreas_koehler
Creator II
Creator II

Calendar: Struggling with min- and max-Dates

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

1 Solution

Accepted Solutions
PrashantSangle

Hi,

try,

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


Or


Num#(Date) as %TimeStamp,

Note : when you apply cross join on date field then while loading date field converted into number to text

There fore your conversion Timestamp# wont work on it. And convert data from Text to num  you have to use Num#().

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

21 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II
Author

Peter,

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

Thanks for your quick reply.

%timestamp.PNGTempMinMaxTime.PNG

HirisH_V7
Master
Master

Hi,

Can you post your time stamp data in Excel.

HirisH
“Aspire to Inspire before we Expire!”
andreas_koehler
Creator II
Creator II
Author

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
Partner - Champion III
Partner - Champion III

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
Master
Master

Post some sample records too look into ..

HirisH
“Aspire to Inspire before we Expire!”
jagan
Luminary Alumni
Luminary Alumni

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