topic Problem to get the last date with peek in QlikView

Problem to get the last date with peek

julruiz123 — Thu, 24 Nov 2011 20:29:13 GMT

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.

Problem to get the last date with peek

swuehl — Thu, 24 Nov 2011 22:24:37 GMT

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

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).

Problem to get the last date with peek

montero91 — Thu, 24 Nov 2011 22:35:21 GMT

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

Problem to get the last date with peek

Fri, 25 Nov 2011 08:02:02 GMT

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

Problem to get the last date with peek

julruiz123 — Fri, 25 Nov 2011 14:15:50 GMT

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

Re: Problem to get the last date with peek

rwunderlich — Fri, 25 Nov 2011 22:51:09 GMT

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

Re: Problem to get the last date with peek

julruiz123 — Sat, 26 Nov 2011 02:54:16 GMT

Thanks Rob,

It works perfect!