topic Re: Max date not today in QlikView

Max date not today

Wed, 23 Apr 2014 12:02:59 GMT

How do I set the max date in my calendar to the max date in my data. Currently i have;

LET vDateMax = Floor(Today());

This is incorrect, as I don't have data up to today, so I don't want those days after the maximum date in my data to be loaded.

Re: Max date not today

tresB — Wed, 23 Apr 2014 12:06:10 GMT

Try like:

Temp:

Load

Max(DateField) as MaxDate

From <your data source>;

Let vDateMax=Num(peek('MaxDate', -1, 'Temp'));

Re: Max date not today

Anonymous — Wed, 23 Apr 2014 12:07:27 GMT

you should use a temporary table

tmpFact:

load

max(Date) as MaxDate

from Facttable

then

Let vDateMax = Peek('Maxdate',0,'tmpFact')

Hope that helps

Re: Max date not today

ashfaq_haseeb — Wed, 23 Apr 2014 12:08:34 GMT

Hi,

I would suggest you to use where exsists function.

I believe you have master calendar,

Just take your master calendar to last tab

and write the below code at the end.

load * from calendar.qvd

where Exsists(Date,Date).

Hope it help.

Regards

ASHFAQ

Re: Max date not today

Fri, 25 Apr 2014 08:55:26 GMT

Hi Tresesco,

when I enter my data source in place of <your data source>, i sometime get a load error in my script saying the DateField can't be found.

The data source in this case is another qvw I am doing a binary load from.

Temp:

Load

Max(SessionStartedDate) as MaxDate

From \\fsdata01\Department\Logging_DataLoad.qvw;

Let vDateMax=Num(peek('MaxDate', -1, 'Temp'));

what am I doing wrong?

Re: Max date not today

tresB — Fri, 25 Apr 2014 10:51:08 GMT

If you are using binary load, you have to refer the loaded table using resident like:

Temp:

Load

Max(SessionStartedDate) as MaxDate

Resident <table name>;

Let vDateMax=Num(peek('MaxDate', -1, 'Temp'));