Problem to get the last date with peek

[julruiz123]

Hi,

I have the next table:

Cod     Date

1          2011-01-11

2          2011-03-14

3          2011-04-15

4          -

5          -

I need to create a calendar, then i used this code

LET varMinDate = Num(Peek('Date',0,'Key_Table_For'));

LET varMaxDate = Num(Peek('DAte',-1,'Key_Table_For'));

When i load the script i have a issue because the last record there isn't date.

How i get the last date and no the last record ??

Thanks.

[swuehl]

Hi,

you could probably check the Date field from end to start on a Date type value, then stop, like

Key_Table_For:

LOAD * INLINE [

Cod,     Date

1,          2011-01-11

2,          2011-03-14

3,          2011-04-15

4,          -

5,          -

];

LET varMinDate = Num(Peek('Date',0,'Key_Table_For'));

for i = NoOfRows('Key_Table_For')-1 to 0 step -1

LET varMaxDate = Peek('Date',$(i),'Key_Table_For');

exit for when isnum($(varMaxDate));

NEXT

LET varMaxDate = num($(#varMaxDate));

Hope this helps,

Stefan

P.S.

Under very special conditions (only valid Dates or NULL in field Date and only unique Date values, you could also try fieldvalue('Date',fieldvaluecount('Date') ) instead of the peek() for the last date record).

[montero91]

Hi,

Do you have more Colum?

why you do not use in Scrip
WHERE Date <>'-';in the Load, by not to charge the lines.

What has worked for me to generate a calendar is to use a date base or minimum date, and the variable Today (), just like that today is my always maximum.

I hope you understand me, use google translators

[#4]

Hi,

You can do a resident load from your table. Use a where clause like

     WHERE NotIsNull(Date)

and sort the table by date

     ORDER BY Date ASC

You can use the Peek function against your new table, which does not contain any null values.

Regards,

Sebastian

[julruiz123]

Hi,

I can't change de Load because there are other columns, so i decided to follow the suggest from Montero. I did it in this way

LET varMinDate = Num(Peek('Date',0,'Key_Table_For'));

LET varMaxDate = Num(Today());

Thanks

[rwunderlich · Accepted solution]

How about this?

minmax:

LOAD

  min(Date) as MinDate,

   max(Date) as MaxDate

RESIDENT mytable;

LET vMinDate = peek('MinDate');

LET vMaxDate = peek('MaxDate);

DROP TABLE minmax;

-Rob

[julruiz123]

Thanks Rob,

It works perfect!