Incompatibility the ClearError() with the Join()

Solved3.34K viewsFormulas and Functions

Hi all,

FYI. [b:3ogmvrrq]ClearError()[/b:3ogmvrrq] can not catch an [u:3ogmvrrq]error-value[/u:3ogmvrrq] from [b:3ogmvrrq]Join()[/b:3ogmvrrq] and can not display [u:3ogmvrrq]replacement-value[/u:3ogmvrrq].

For details, see the model file. The model is rather simple and self-explanatory. You need to enter the Start and End. If Start will be greater then End, then QM should display Alert 3 times in the Report items group. This is exactly what QM does, except the item SubSales.

I wonder whether will be any response from the QM-team or not?

See the model file: [b:3ogmvrrq]QM5.3.7 Incompatibility the ClearError() with the Join(). SAU[/b:3ogmvrrq]

Selected answer as best

Hi Sau,

The explanation for this is as follows:

The function sublist() itself will not work if the value in the "from" argument is structurally greater than the value in the "to" argument. As you can see from the dependency inspector this will return a #value error.:


[b:2agagbgd]Join then cannot operate on a #value error [/b:2agagbgd]so it then returns another #value error:


The reason this may seem un-intuitive is the fact that join() will always return a [b:2agagbgd]string[/b:2agagbgd], even if that string is an error (#value/div/0 etc.). This value is then passed to the clearerror() function which determines that the string "#value" does not contain an error and thus doesn’t use the "replacement value".

The bottom line is that join() does not propagate errors.

Kind Regards,


PS. You can actually simplify this formula further than my original response would suggest:

[code:2agagbgd]SubSales = Join( clearerror(SubList( M1::Sales, Start, End ), Alert ),",") [/code:2agagbgd] :)

Selected answer as best
You are viewing 1 out of 5 answers, click here to view all answers.