How do you set a Min and Max date based off different fields within the same table?

[#1]

Hi,

Could anyone offer some advice on the following.....

I am currently using the following symtax to try and set the min and max dates within my document:

 LET varMinDate = Num (Peek('EstProjStart', 0,'Projects')); 

LET var

 varvarMaxDate = Num (Peek('EstProjFin', -1,'Projects'));

I appreciate that I need to order the Projects table, however, because I need to take the minimum value from one field and and the maximum value from another field I am not quite sure how to best tackle this. I am also bringing this data in from excel not access like in a lot of the course material. The Projects table symtax can be seen below:

Projects:

 LOAD









RegionID,

 //EstProjStart,









PhaseProjManager As ProjectManager,
FROM
[Data files\Natural Resources data.xlsx]
(
 ooxml, embedded labels, table is Projects); //ActProjFin, //EstProjFin, //ActProjStart, ProjectName,
ProjectDescription
 ,

Any guidance would be most appreciated.

Thanks,

Carl

[disqr_rm]

See an excellent way of doing that here:

http://qlikviewmaven.blogspot.com/2009/06/quick-load-of-max-field-value.html

[#3]

Thanks Rakesh that is very helpful.

I am still having problems though with the master calendar picking up the max and min values though however.

I have added a Max table and a min table, and pointed my let variable statements at them as below:

LET varMinDate = Num (Peek('EstProjStart', 0,'MinEstProjStart'));


LET varMaxDate = Num (Peek('EstProjFin', -1,'MaxEstProjFin'));

My document is recognising the min and max values, but this is not being picked up in my Master Calendar.

Any suggestions?

Thanks,
Carl

[disqr_rm]

What you can check is that rour fields have correct date data types in there. Try to take out NUM function and see what you get in your variables.

Also check all the records in your Min and Max tables. Do you have more than one records on those tables?

What do you get in your var* variables?

[#5]

Hi Rakesh,

I'm sure that I am pretty close to fixing this but not quite....My year table is only calculating my dates from the EstProjStart field, I need to it select the Minimum date from that but the Maximum date from the EstProjStart field. I've followed your instructions on creating new new tables which identifies the minimum values and the maximum values from the two fields. My Master Calendar code is below:

LETvarMinDate = Num (Peek('EstProjStart', 0,'MinEstProjStart'));

LETvarMaxDate = Num (Peek('EstProjFin', 0,'MaxEstProjFin'));

LETvarToday = Num (today());

//Create all days in the range from varMinDate to varMaxDate

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATETempDate AS EstProjFin,

1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

//********** Master Calendar **********

MasterCalendar:

week(TempDate) AS Week,

year(TempDate) AS Year,

month(TempDate) AS Month,

day(TempDate) AS Day,

weekday(TempDate) AS WeekDay,

'Q' & Ceil(Month(TempDate)/3) AS Quarter,

date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

week(TempDate)&'-'&Year(TempDate) AS WeekYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

RESIDENT

TempCalendar ORDER BY TempDate Asc;

DROP

TABLE TempCalendar;

Is it the temporary calendar which I am not using correctly to pull in two tables?

Thanks,

Carl

[disqr_rm]

Hi Carl,

I would do it little differently (may be cleaner way). Have a look and let me know your thoughts.

Rakesh

[#7]

Thanks Rakesh - That works great. I had definitely over complicated it...

[#8]

Hi Rakesh,

Sorry to ask you another question but it is linked to the above. As an extension to the above I am looking to input some production data for the projects. The production table is very simple with three fields: ProjectName, Date, ProductionAmount. When I bring this information in, I get a script error as follows:

Script line error:
LOAD date( - recno() + 1) as Date
AUTOGENERATE NoOfDays

Why would the production table be conflicting so much with the Calendar Table? They should both be linked via the Date field....

Any suggestions would be most appreciated.

Thanks,

Carl

[disqr_rm]

Have a look at following line in your code:

LOAD date( - recno() + 1) as Date

You are missing the variable before "- recno()". Or it could be that your variable varMaxDate doesn't have any value. Check displaying that variable value in a text box and see what you get.

Hope this helps.

[#10]

In my calendar table I have the variable you are talking about:

MasterCalendar:



 

LOAD
*,
week(Date) AS Week,
year(Date) AS Year,
month(Date) AS Month,
day(Date) AS Day,
weekday(Date) AS WeekDay,
'Q' & Ceil(Month(Date)/3) AS Quarter,
date(monthstart(Date), 'MMM-YYYY') AS MonthYear,
week(Date)&'-'&Year(Date) AS WeekYear,
Year2Date(Date, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(Date,-1, 1, $(vToday))*-1 AS LastYTDFlag;



LOAD
date($(varMaxDate) - recno() + 1) as Date
AUTOGENERATE
NoOfDays;

The code for the Production table is as follows:

Production:



 

LOAD
ProjectName,
Date,
ProductionAmount

FROM




[Data files\Natural Resources data.xlsx]
(ooxml, embedded labels, table is Production);