Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to get max(OpenDate) from a table in resident load, but not getting its showing error like invalid expression
any suggestion plz. OpenDate format is in : 02-08-2017
See below screen shot.
Thanks in advance
Rajesh
Hi,
Load it in a temp table and store the result in a Variable.
Temp:
LOAD
Min(DateFieldName) as MinDate,
Max(DateFieldName) as MaxDate
Resident TableName;
Refer the below thread:
Hi,
I thing you forget to use Group By in script
Load *,
Max(OpenDate) as CurrentDate
Resident Incident
Group By
Your all fields here that you load in this table ;
Ex:-
Load
ID,
Max(OpenDate) as CurrentDate
Resident Incident
Group By ID;
Regards,
Anand
Thanks Varun for u r immidiate responce,
my load statement like
Load *,
Max(OpenDate) as CurrentDate
Resident Incident;
but it showing invalid expression error
In Incident how many fields are there just check and use them in your Group By statement.
Regards
Anand
Hi Rajesh,
You Forgot to group use group by.
Load *,
Max(OpenDate) as CurrentDate
Resident Incident;
In Your below Script, You need to mention alll the field which is there in *.
Please revert for more Clarity and if it is not resolved.
Regards,
Vanraj Dinesh Bohra
Hi Rajesh,
Here's another method,
MaxOpenDate:
Load max(Temp) as MaxOpenDate;
LOAD FieldValue('OpenDate',IterNo()) as Temp
AutoGenerate 1 While not IsNull(FieldValue('OpenDate',IterNo()));
Let vMaxOpenDate = Floor(Peek('MaxOpenDate',0,'MaxOpenDate'));
DROP Table MaxOpenDate;
If you have a large dataset from which to obtain your max date you will find this will run quicker because rather than go through maybe millions of lines this code goes through the distinct field values of OpenDate which will number maybe only a few hundred.
Cheers
Andrew
Thank you All for u r valid suggestions
i was looking for NoOfDates between Current and previous date
done by using precising load ---Floor(Today()- OpenDate) as Days
Regard
Rajesh