Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chriys1337
Contributor

Can't use max() function in script?

Hi there,

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?

Any help really appreciated.

Chris

1 Solution

Accepted Solutions

Re: Can't use max() function in script?

Now that I think about it, you're always using a GROUP BY clause, whether explicitly specified or implied. In the second case, the implied clause is something like:

LOAD Max() AS MaxField

FROM ...

GROUP BY Nothing;

which of course isn't valid script syntax.

7 Replies
Highlighted
sureshqv
Esteemed Contributor III

Re: Can't use max() function in script?

max will work   with group by

sureshqv
Esteemed Contributor III

Re: Can't use max() function in script?

Re: Can't use max() function in script?

May be try this

Table:

LOAD *

FROM [Test.qvd] (qvd);

Left Join (Table)

LOAD Max(SCFI_date_num) as SCFI_Max

Resident Table;

Re: Can't use max() function in script?

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.

psilva001
Contributor

Re: Can't use max() function in script?

You must load all fields, after, concatenate your specific field with expressions, etc

As in the attached file









Re: Can't use max() function in script?

Now that I think about it, you're always using a GROUP BY clause, whether explicitly specified or implied. In the second case, the implied clause is something like:

LOAD Max() AS MaxField

FROM ...

GROUP BY Nothing;

which of course isn't valid script syntax.

chriys1337
Contributor

Re: Can't use max() function in script?

Thank you all, I know now what my mistake was (the missing group by). My solution to this is, to load the max value in a second load, as described by Sunny, but without joining it in the end.

Community Browser