Incompatibility the ClearError() with the Join()

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
Solved2.11K viewsFormulas and Functions
0

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
0

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.:

[attachment=1:2agagbgd]sublistvaluerror.png[/attachment:2agagbgd]

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

[attachment=0:2agagbgd]sublistvaluerror2.png[/attachment:2agagbgd]

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,

James

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
0

Hi SAU,

The formula below will achieve the desired effect.

[code:1ldaa5dz]SubSales = ClearError( Join( clearerror(SubList( M1::Sales, Start, End ), Alert ),","), Alert )[/code:1ldaa5dz]

Regards,

James

0

Hi James,

Thank you for your response. The formula is really working. However, the expression of the functions: ClearError(Join(ClearError(…))), – is not obvious.

Maybe you will make the minimum explanations once.

0

[quote:1iq9fiy0]Join then cannot operate on a #value error so it then returns another #value error:[/quote:1iq9fiy0]

Apologies this should say: [b:1iq9fiy0]Join operates on the #value as a string and returns the[i:1iq9fiy0] string[/i:1iq9fiy0] #value.[/b:1iq9fiy0]

James

0

Hi James,

Thank you for the comprehensive formula behavior explanation and the formula refactoring.

Latest Questions