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

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

Wed, 03 Feb 2010 14:38:08 GMT

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

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

disqr_rm — Wed, 03 Feb 2010 14:48:02 GMT

See an excellent way of doing that here:

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

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

Wed, 03 Feb 2010 15:17:04 GMT

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

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

disqr_rm — Wed, 03 Feb 2010 16:15:10 GMT

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?

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

Wed, 03 Feb 2010 17:27:21 GMT

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

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

disqr_rm — Wed, 03 Feb 2010 23:21:50 GMT

Hi Carl,

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

Rakesh

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

Thu, 04 Feb 2010 11:07:08 GMT

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

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

Thu, 04 Feb 2010 13:51:39 GMT

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

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

disqr_rm — Thu, 04 Feb 2010 14:38:37 GMT

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.

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

Thu, 04 Feb 2010 14:59:50 GMT

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

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

disqr_rm — Thu, 04 Feb 2010 15:13:38 GMT

What's the varMaxDate value? Just put it in a text box and post here.

Also where are you defining varMaxDate? LET varMaxDate = ?????.

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

Thu, 04 Feb 2010 15:26:26 GMT

The production dates fall within the varMaxDate and the varMinDate.

I have no decided to place the production table after the calendar table and it now works - but would prefer to have the production table in the facts tab which is before the calendar.

This is my code (I have defined the Let statements before the calendar table on the calendar tab:

Let varMinDate = peek('MinEstProjStart');

Let varMaxDate = peek('MaxEstProjFin');

Let varToday = today();

Let NoOfDays = varMaxDate - varMinDate + 1;

drop table MinMaxTab;

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;

//********** Production Table **********

Production:

LOAD

ProjectName,

Date,

ProductionAmount

FROM

[Data files\Natural Resources data.xlsx]

(ooxml, embedded labels, table is Production);

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

disqr_rm — Thu, 04 Feb 2010 15:42:17 GMT

oh! ofcourse, if you had a Date field before load, that would't work. What you could do is use some other field name than Date in the Calendar, and later rename it or load it again in pre-load as Date.

Example:

LOAD *,
week(dummyDate) AS Week,
...
...
dummyDate as Date;
LOAD
date($(varMaxDate) - recno() + 1) as dummyDate
AUTOGENERATE
NoOfDays;

drop field dummyDate;

Anyway, glad that it is working for you now.