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.
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.
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
Temp:
LOAD *,
min(QtrDates) as MinDate,
max(QtrDates) as MaxDate
Resident CurrentQtr Group by fieldname;
Drop Table CurrentQtr;
Hi vishwaranjan,
Your script will return the erro r: invalid expression.
Regards,
Snehal Nabar
Hi vishwaranjan,
Your script will return the error: invalid expression.
Regards,
Snehal Nabar
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
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
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.
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