Max date not today

[#1]

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.

[tresesco · Accepted solution]

Try like:

Temp:

Load

          Max(DateField) as MaxDate

From <your data source>;

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

[Anonymous]

you should use a temporary table

tmpFact:

load

max(Date) as MaxDate

from Facttable

then

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

Hope that helps

[ashfaq_haseeb]

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

[#5]

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?

[tresesco]

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