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

[andreas_koehler]

Hi Peter,

thanks for pointing out that the %TimeStamp is left-aligned and that this indicates a text strings.

Timestamp changed: TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]' to 'DD.MM.YYYY hh:mm'

This is now identical to DateFormat.

Yet the table is still without values.

Regarding the interpretation function Date#() and TimeStamp#() my understanding was that both do the job of ensuring hat the string is understood as dual field and represented as date serial number. I used TimeStamp#() instead of Date#() to keep the fractions. I also used TimeStamp#() to generate the calendar.

[andreas_koehler]

Hi Jagan,

Num(Min(%TimeStamp)) changed to Min(%TimeStamp)

Num(Max(%TimeStamp)) changed to Max(%TimeStamp)

Still no values in the TempMinMaxTime in both fields.

Comment: I found the Num() function used in the book QV11fD which was
Num(Date#(Min(Period), 'YYYYMM')) as MinDate, etc.

My understand was that that as I used TImestamp#() go generate %TimeStamp it is already understood as a dual field and with Num() I could pass the numerical value to MinDate.

Where is my misunderstanding?

Regards,

Andreas

[andreas_koehler]

Hirish, Marco,

Please find attached an excerpt from the source table. These are time series of values for T1 etc.

I read this table via

CrossTable(TCode, #Data)

LOAD *

FROM

[$(vSource)TSource.xlsx]

(ooxml, embedded labels, table is Data);

stored it and loaded it

as

LOAD Timestamp#(Date) as %TimeStamp,

        Date as ImportedDate,

         TCode as %TCode,

     #Data

FROM

...(qvd);

Regards,

Andreas

[marcus_sommer]

I think your format from field Date is slightly different to your declaration of the format-variables (maybe there are also several different formatings ?) and therefore your converting failed but without a few examples it's difficult to say what's wrong.

- Marcus

[#6]

Hi man,

please try this way  it will come results

data:

load * inline [

code,Data,Date

101,2000,2/7/2015

102,5000,6/7/2015

103,3244,20/7/2015

104,3288,28/7/2015

105,67000,3/8/2015

106,4500,17/8/2015

107,56000,25/8/2015

108,2900,4/10/2015

109,4399,18/10/2015

110,3499,3/11/2015

111,8700,15/11/2015

112,5490,5/12/2015

113,4300,6/1/2016

114,2400,14/1/216

115,5400,4/2/2016

];

Temp:

load

num(min(Date)) as MinDate,

num(max(Date))as MaxDate

Resident data;

Temp1:

load //Timestamp(MaxDate,'D/M/YYYY HH:MM:SS') as MaxDates, // if your using this way coming  the time stamp

//Timestamp(MinDate,'D/M/YYYY HH:MM:SS') as MinDates//if your using this way coming the  time stamp

Timestamp#(MinDate,'D/M/YYYY HH:MM:SS') as MinDates,//if your using this way coming the  time stamp Number

Timestamp#(MaxDate,'D/M/YYYY HH:MM:SS') as MaxDates//if your using this way coming time stamp Number

resident Temp;

let vmindate=Peek('MinDates',0,'Temp1');

let vmaxdate=Peek('MaxDates',-1,'Temp1');

regards,

sreenivas

[andreas_koehler]

Marcus,

I guess you refer to the excel sheet. Thanks for pointing out that the format in the first column differs from what is in the cell.

What I did now is to change the setting of timeStamp to TimestampFormat='DD.MM.YYYY hh:mm:ss'

I did also format the excel column similar to the raw data.

Now everything excel cell format, column format and timeStamp format is identical.

Still no values in the MinMaxTable and %TimeStamp is still left-aligned.

-Andreas

[marcus_sommer]

The best would be you provide a small example of your data and app.

- Marcus

[marcus_sommer]

Try it without Timestamp#(Date) as %TimeStamp within the qvd-load only Date and if this won't worked make this converting after the crosstable-load in an additionally resident-load and store this. To avoid any transforming within a qvd-load is important to be able to load a qvd optimized especially if you have larger amounts of data.

Edit: To get the max/min values is easier with: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook

- Marcus

[PrashantSangle · Accepted solution]

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,

[andreas_koehler]

Marcus,

thanks for the reference to Rob's blog. I have also seen and bookmarked your How-to-use Calendar.

Right now I try to focus on resolving this one issue. I added source code and excerpt in a post: 10.02.2016 04:29