topic Figures format issue (. and ,) in Search the Community

Figures format issue (. and ,)

Calogero — Sun, 09 Jan 2022 09:04:12 GMT

Hi dear experts,

Hope you are doing well and Happy New year 2022!

I need you help regarding figures format.

Hereunder the 2 columns mentionned figures in my data base.

I have the issue in the Net Value in DC column an a second one related in the Net Value in EUR colum.

For example for the Net Value in DC

59.809.5666.114.018 = 59809,56 USD (I'm from Belgium so I don't use "." but "," as decimal separtor)

1.148.647 = 1148,65 EUR

For the same transaction but in the Net Value in EUR

52710,2X €

1148,65 EUR

I tried to change the figure format in Qlik Sense Script.

Thank you,

Regards.

Re: Figures format issue (. and ,)

Taoufiq_Zarra — Sun, 09 Jan 2022 13:47:37 GMT

@Calogero

have you tested to change

or you can use replace() function ?

Re: Figures format issue (. and ,)

Calogero — Sun, 09 Jan 2022 14:21:49 GMT

Hi dear Taoufiq,

Thanks for your help.

Sorry but I'm still a novice in the Qlik Sense Script.

Find hereunder the script linked to the data table.

The issue is linked to the 2 line in Bold.

Where I have to put "SET DecimalSep=',';

and SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

LOAD
S.org.,
"Bill. type",
Date(Floor(Date#("Creation date",'DD.MM.YYYY')),'DD/MM/YYYY') as "Creation_date",
Invoice,
Billto,
"Billto name",
Billto&'|'&"Billto name" as "CustomerPK",
Date(Floor(Date#("Fixed value date",'DD.MM.YYYY')),'DD/MM/YYYY') as "Fixed_value_date",
"Payment terms",
"Payment terms desc",
"Due date",
Date(Floor(Date#("Clearing date",'DD.MM.YYYY')),'DD/MM/YYYY') as "Clearing_date",
"Delay days",
"Net Value in DC",
"Doc. Curr.",
"Net Value in EUR"

Thanks 🙂

Re: Figures format issue (. and ,)

Calogero — Mon, 10 Jan 2022 14:27:27 GMT

Dear Experts,

I need your help. 🙂

Re: Figures format issue (. and ,)

Taoufiq_Zarra — Tue, 11 Jan 2022 12:01:49 GMT

@Calogero can you share a sample data ?

Re: Figures format issue (. and ,)

Or — Tue, 11 Jan 2022 12:15:37 GMT

I'm not seeing an issue with the separator.

The EUR column is formatted in scientific notation, which means the comma is used as a decimal separator (the number in the first row is a little over 5 1/4, times 10 to the 13th power).

Re: Figures format issue (. and ,)

Calogero — Thu, 13 Jan 2022 07:36:38 GMT

Hi dear @Taoufiq_Zarra , find attached my data set.

The figures are mentioned in the last columns.

Thank you.

Regards.