I am having some problems to use the max() function in the script, in the frontend it works.
I am trying to find my maximu !m value of the field SCFI date. I have already formated a newly generated field which shows the dates numerical. But there is still an issue with calculating the maximum.
max(SCFI_date_num) as SCFI_Max
The script generator stops with the hint:"Invalid expression", which is totaly nonsense... why should the max() function does not work anymore?
Max() is an aggregation function. It aggregates values from multiple records into a single value.
You can use Max() in a load script in two ways:
With a GROUP BY clause, as Chanty 4u suggested
Without a GROUP BY clause. In that particular case, you'll search for a maximum value in an entire table and you cannot use any other fields in the same LOAD statement without passing them as parameter to an aggregation function.
In your case, the Max() function will be accepted if you omit the *, part.