MIN and MAX Date in Load Script

[Anonymous]

Hello!

Could someone help me show me how to add 2 new fields for the MIN and MAX  of the field QtrDates... I'm trying to get the MIN and MAX dates of the Current Quarter (QtrDates)... I tried loading it but it keeps saying invalid function... I just don't know exactly which part of the scrpt  should I insert this.

You may want to look at the attached file to a better undestanding...

Tks.

[#2]

Hi,

You can use the below script for calculating the minimum and the maximum date of QtrDates and store this results in variables.This result stored in variable can be used whenever required.

Temp:

LOAD min(QtrDates) as MinDate

Resident CurrentQtr;

Temp1:

LOAD max(QtrDates) as MaxDate

Resident CurrentQtr;

LET vMinDate = num(floor(peek(MinDate,0,Temp)));

LET vMaxDate = num(floor(peek(MaxDate,0,Temp1)));

Drop Table Temp,Temp1;

Hope this helps,

Regards,

Snehal Nabar

[#3]

Temp:

LOAD *,

min(QtrDates) as MinDate,

max(QtrDates) as MaxDate

Resident CurrentQtr    Group by fieldname;

Drop Table CurrentQtr;

[#4]

Hi vishwaranjan,

Your script will return the erro r: invalid expression.

Regards,

Snehal Nabar

[#5]

Hi vishwaranjan,

Your script will return the error: invalid expression.

Regards,

Snehal Nabar

[Anonymous]

Snehal,

I tried this but I got this error message...pls. take note that the QtrDates came from a joint table...Does it have someting to do with it?

Field not found - <QtrDates>

Temp:

LOAD min(QtrDates) as MinDate

Resident CurrentQtr

[#7]

Hi,

Check whether CurrentQtr table is loaded before the above mentioned script.

As Temp table uses resident load to load data from CurrentQtr table to  perform calculations.

Regards,

Snehal Nabar

[jagan · Accepted solution]

Hi,

Try like this

Temp:

LOAD

     Min(DateFieldName) as MinDate,

     Max(DateFieldName) as MaxDate

Resident TableName;

LET vMinDate = floor(peek('MinDate'));

LET vMaxDate = floor(peek('MaxDate'));

Drop Table TableName;

Now the two variables vMinDate and vMaxDate has min and max dates respectively.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Snehal,

Oh!  i got it  now,,... I forgot to name a new table when I did a left join.  This work perfectly now... However, how come the results  showed up as numbers when my  Qtrdates field is already in date format?  I tried using the DATE# and Date function but to no avail. 

Thanks