Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Problem to get the last date with peek

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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Problem to get the last date with peek

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

View solution in original post

6 Replies
Highlighted
MVP
MVP

Problem to get the last date with peek

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

Highlighted
Creator
Creator

Problem to get the last date with peek

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

Highlighted
Not applicable

Problem to get the last date with peek

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

Highlighted
Partner
Partner

Problem to get the last date with peek

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Problem to get the last date with peek

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

View solution in original post

Highlighted
Partner
Partner

Re: Problem to get the last date with peek

Thanks Rob,

It works perfect!