topic Converting a number to Standard NUmber Format in QlikView

Converting a number to Standard NUmber Format

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

Dears,

My txt file contains the following number: -2432445,184, which is equal to: -2.432.445.185,00.

How can I transform this number: -2432445,184 to show R$ -2.432.445.185,00?

I'm trying to do it during the loading process, but It's not working.
I'm using the following formula:

Money(Money#(@25, '#,##0.00;-#,##0.00', '.', ','), 'R$ #.##0,00;-R$ #.##0,00', ',', '.')

How can I do it?
Thanks

Re: Converting a number to Standard NUmber Format

raji6763 — Fri, 15 May 2020 08:02:09 GMT

Try this:

It's Working for me.

number:

load
Money(Money#(number, '#,##0.00;-#,##0.00', '.', ','), 'R$ #.##0,00;-R$ #.##0,00', ',', '.') as no;

LOAD
"number"
FROM [lib://DataFiles/number.txt]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

Re: Converting a number to Standard NUmber Format

brunolelli87 — Fri, 15 May 2020 13:37:57 GMT

Hello,

Thanks for your help, but it's not working...

I'm from Brazil, so my standard decimal separator is "," and my standard thousand separator is ".".
At the very first tab on Script I have the following code:

SET ThousandSep='.'; SET DecimalSep=','; SET MoneyThousandSep='.'; SET MoneyDecimalSep=','; SET MoneyFormat='R$#.##0,00;-R$#.##0,00'; SET TimeFormat='hh:mm:ss'; SET DateFormat='DD/MM/YYYY'; SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'; SET FirstWeekDay=6; SET BrokenWeeks=1; SET ReferenceDay=0; SET FirstMonthOfYear=1; SET CollationLocale='pt-BR'; SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez'; SET LongMonthNames='janeiro;fevereiro;março;abril;maio;junho;julho;agosto;setembro;outubro;novembro;dezembro'; SET DayNames='seg;ter;qua;qui;sex;sáb;dom'; SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';

But, enabling or not this tab, does not change the final result.

Any other suggestions?

Re: Converting a number to Standard NUmber Format

brunolelli87 — Fri, 15 May 2020 16:07:01 GMT

Hello guys,

Please, take a look at my analysis...

I'm from Brazil, so my Decimal separator is "," and my thousand separator is ".".
My Excel spreadsheet contains some values expressed as: "-2432445,184", meaning "-2.432.445.184,00".

Please, check the solutions that I had tried and the results I got:

First attempt: Money(Money#...

Money(Money#(@25, '#,##0.00;-#,##0.00', '.', ','), 'R$ #.##0,00;-R$ #.##0,00', ',', '.') as LOPP

Second attempt: Money(Num#...

Money(Num#(@25, '#,##0.00;-#,##0.00', '.', ','), 'R$ #.##0,00;-R$ #.##0,00', ',', '.')

Third Attempt: Just Num()

Num(@25) as LOPP // Lucro/Prejuízo do Período.

Fourth attempt: Changing the ',' and '.' places

Money(Money#(@25, '#.##0,00;-#.##0,00', ',', '.'), 'R$ #.##0,00;-R$ #.##0,00', ',', '.') as LOPP

So, what else can I do?

I have no ideas!!!

Re: Converting a number to Standard NUmber Format

tabletuner — Fri, 15 May 2020 21:41:52 GMT

the solutions you tried, work at my test environment.

Could you share (a sample of) the source file?