Converting a number to Standard NUmber Format

[brunolelli87]

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 

[raji6763 · Accepted solution]

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);

[brunolelli87]

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?

[brunolelli87]

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

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!!!

[tabletuner]

the solutions you tried, work at my test environment.

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