Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

julruiz123
Not applicable

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
rwunderlich
Not applicable

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

6 Replies
swuehl
Not applicable

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

montero91
Not applicable

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

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

julruiz123
Not applicable

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

rwunderlich
Not applicable

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

julruiz123
Not applicable

Re: Problem to get the last date with peek

Thanks Rob,

It works perfect!