topic Re: expression error can not be avoided use if condition in QlikView

expression error can not be avoided use if condition

Anonymous — Thu, 15 Oct 2015 14:48:28 GMT

Hi, all, I just updated to QV 11.20 SR12, but now I encountered a problem.

For expression as below use to format the money:

= money($(_vCost), $($(vCurrency)) & ' #,##0.000')

Which is used in our old dash, and before, even if the expression is not correct when vCurrency = '-', there is no error info.

I know it is good that in the new version, it will detecte such error and display:

"Error: Error in expression: ')' expected"

So I modify my script as following to use if condition to process when vCurrency = '-':

=if(vCurrency = '-', 'N/A', money($(_vCost), $($(vCurrency)) & ' #,##0.000'))

But surprisingly , seems because part of the script "money($(_vCost), $($(vCurrency)) & ' #,##0.000')" is wrong, then I still get the error info.

I tested to use like :

=if(vCurrency = '-', 'N/A', $(_vCost)),

Then it is correct, which means the if condition is correct.

Did I miss anything? Dose anyone have any idea to avoid this?

Thanks very much

Re: expression error can not be avoided use if condition

nagaiank — Thu, 15 Oct 2015 15:24:43 GMT

Try

money($(_vCost), Chr(39) &$(vCurrency)& ' #,##0.000' & Chr(39)

Re: expression error can not be avoided use if condition

Anonymous — Thu, 15 Oct 2015 15:33:16 GMT

NagaianK,

Tried, it dose not work. Thanks.

ZHihong

Re: expression error can not be avoided use if condition

nagaiank — Thu, 15 Oct 2015 15:46:40 GMT

If vCur = '$',

Money(100,'$(vCur)#,###.000')

Num(100,'$(vCur)#,###.000')

Displays $100.000 in textbox.

Re: expression error can not be avoided use if condition

Anonymous — Mon, 19 Oct 2015 09:23:30 GMT

Hi, NagaianK,

Yes, if vCur = '$' or '€', any specific currency symbol, then expression as below works.

Money(100,'$(vCur)#,###.000')

The problems is that when vCur= '-', then the expression dose not work, and will give "Error: Error in expression: ')' expected" error. This is correct. But then I use the if condition to process the error situation:

=if(vCur = '-', 'N/A', money($(_vCost), $(vCur) & ' #,##0.000'))

Then I still got "Error: Error in expression: ')' expected".

But when I try:

=if(vCur = '-', 'N/A', money($(_vCost), ' #,##0.000')),

then it works.

So I guess QV still first check the correctness of expression:

money($(_vCost), $(vCur) & ' #,##0.000')

Re: expression error can not be avoided use if condition

tresB — Mon, 19 Oct 2015 09:27:22 GMT

You are right, qv parses the string to find the correctness of the expression syntax before it starts evaluating the expression. And that makes sense. Alternatively, you could try with ASCII character evaluation for the problematic character, like:

chr(XXX) & ...

Re: expression error can not be avoided use if condition

rwunderlich — Mon, 19 Oct 2015 09:34:49 GMT

The syntax checker has some issues in SR11+

SR11 Error Handling Changes | Qlikview Cookbook

Valid expressions may report "Error in Expression" even when the expression is correct.

-Rob

Re: expression error can not be avoided use if condition

Anonymous — Mon, 19 Oct 2015 10:12:33 GMT

Hi, Rob,

Thanks for the link and the article. I see, the problem in my case seems related to the 2nd error handling in your article "Charts that contain invalid expressions will now fail to render." Good news is that this "Error" will not display in our server side, just in my local development.

But I think it is not a good idea even if condition can not avoid it.

Re: expression error can not be avoided use if condition

Anonymous — Mon, 19 Oct 2015 11:00:04 GMT

Hi, tresesco,

Thanks for your suggestion, I will try it. Actually I also tried to use char "-" or null( ) instead of $(vCur), and then it works. So I think it is because our old way to use $(vCur) cause some problem. I will test more to see why.

Zhihong

Re: expression error can not be avoided use if condition

miguelbraga — Mon, 19 Oct 2015 11:09:11 GMT

Have you tried using isnull statement in the if clause?

Try something like this:

=if(isnull(vCur), 'N/A', money($(_vCost), '$(vCur)#,###.000'))