Skip to main content
Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

Converting a number to Standard NUmber Format

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 

1 Solution

Accepted Solutions
raji6763
Creator II
Creator II

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

 

View solution in original post

4 Replies
raji6763
Creator II
Creator II

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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 	LOPPMoney(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', ',', '.')

 

 2.png

Third Attempt: Just Num()

 

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

 

 

 4.png

 

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

 

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

 

 5.png

 

So, what else can I do?

I have no ideas!!!

tabletuner
Creator III
Creator III

the solutions you tried, work at my test environment.

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