topic Re: Trouble applying thousand separators during reload in App Development

Trouble applying thousand separators during reload

Vegar — Fri, 17 Nov 2023 09:57:10 GMT

I have multiple fields in many application that contains money values. I want to standardize the number and money formats by defining a default setup of our System Variables

ThousandSep
DecimalSep
MoneyFormat
MoneyThousandSep
MoneyDecimalSep

When running the script I only get to apply the decimal separators and the currency symbol, I don't get the thousand separators be applied on money() nor num() formating.

I don't know if there is something that I've missed, missunderstood or if this is a bug.

I use this script.

SET ThousandSep=' '; SET DecimalSep=','; SET MoneyFormat='# ##0,00 €;-# ##0,00 €'; SET MoneyThousandSep=' '; SET MoneyDecimalSep=','; Source: LOAD num(round(rand() * 10000, '0.001')) as Value AutoGenerate 10; LOAD Value, money(Value) as Money, text(money(Value)) as Money_text, money(Value, '$(MoneyFormat)') as Money_moneyformat, num(Value) as Num Resident Source; drop table Source;

I expect my new field Money and Money_moneyformat to both have this format: '# ##0,00 €;-# ##0,00 €'

But when i run my script the thousand separator is not applied to the Money field, only the decimal separator and the currency symbol, but the Money_moneyformat is correct.

However, if I wrap text() around my Money calculation I do get the correct format, but the value is stored as a string. This implies that the format is there during the script reload, but is lost before being presented in the application.

See output of my script below (with comments)

Do you guys get the same behavior from the thousand separators if you run my script? Do you know how to fix it so that the separator is applied automatically during reloads ? Or do you know any documentation saying this is how it supposed be?

Re: Trouble applying thousand separators during reload

Anushkaa — Fri, 17 Nov 2023 15:12:21 GMT

Hi, you can use Money#(Money(Value)) as Money. It will give same result as Money_moneyformat field.

Re: Trouble applying thousand separators during reload

rwunderlich — Fri, 17 Nov 2023 18:24:27 GMT

I get the same results as you. I believe this is a long outstanding bug (from QV days) that I never dug into until your post. But I do recall having to use the Money(value,format) form and never understood why.

it looks like the issue is when default format is used in Money(), i.e., no second parameter, spaces are removed from the MoneyFormat format string when it is stored with the field. In my "Money" field results the space between the last digit and the euro symbol is missing. It doesn't appear that way in your image, maybe you can double check.

Money() does not create an explicit dual, but rather stores the format with the field and applies it at display time. Looking at both fields using QSDA Pro, I can see that the format mask stored with the "Money" field is missing spaces whereas the "Money_moneyformat" field does have spaces.

@Anushkaa suggestion of Money#(Money(Value)) is a workaround in that it creates an explicit dual at the cost of increasing the storage required for the field.

https://qlikviewcookbook.com/2017/03/dual-storage-vs-dual-behavior/

The issue is probably worth pursuing as a bug or a doc clarification.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Trouble applying thousand separators during reload

Vegar — Fri, 17 Nov 2023 21:33:43 GMT

Thanks for looking into this @rwunderlich .

Something have happened with the script when copying it into a community post and then back into an Qlik application. When I use my original script I get spacing between the value and € sign, but when I use the code snipplet I posted in this thread I get the same result as you.

However I don't think this issue is limited to blanks. I get this problem even if I don't use blanks as separator. Using the setup as shown below with _ as thousand separator still leaves out the separator when using money()

SET ThousandSep='_'; SET DecimalSep=','; SET MoneyFormat='#_##0,00 €;-#_##0,00 €'; SET MoneyThousandSep='_'; SET MoneyDecimalSep=',';

I am attaching a qlik sense qvf file with the correct blanks that separates the number and the € when using money().