Converting from text to number with abbreviation!

[brunolelli87]

Dears,

I have an Excel Spreadsheet with some text cells like: 5.122,88 M or 1.2 B, where M means Million and B mean Billion. 

I would like to load it and convert it into a number (currency), so, instead of having 5.122,88 M, I would like to have "USD 512.288.000.000,00", and instead of 1.2B, I would like to have "USD 1.200.000.000,00".

How can I do it?
My preference is to do it while loading the database, so I can easily work with it.

Thanks for your attention,
Best regards

[Lisa_P]

Try this sample script to show how to do a conversion.

You could add the USD component on the front end so it is treated like a number

Map_Abb:
Mapping load * inline[
    Abb, Mult
    M, 1000000
    B, 1000000000
];

[Table]:
LOAD * INLINE
[
    Num
    5.122 M
    1.2 B
];

Left Join(Table)
Load *,
  Num(NumA*(ApplyMap('Map_Abb', NumB))) as FinalNum;
Load [Num],
  Subfield([Num], ' ', 1) as NumA,
  Subfield([Num], ' ', 2) as NumB
Resident Table;

[Taoufiq_Zarra]

I have some doubts, but maye be :

first :

SET ThousandSep='.';

Num(pick(wildmatch(A,'*M','*B'),Num(purgechar(A,'M.')),Num(purgechar(A,'B.')))*100000000,'USD #.##0,00') as New_A,

A is your field

the complete example :

Data:

load Num(pick(wildmatch(A,'*M','*B'),Num(purgechar(A,'M.')),Num(purgechar(A,'B.')))*100000000,'USD #.##0,00') as New_A,* inline [
A
5.122,88 M
1.2 B
] (delimiter is '|') ;

output :

[brunolelli87]

Hello,

Thanks for your quick response, but it's not exactly what I was looking for.

In your code, the system is multiplying by 100000000 independently if it's M (million) or B (billion).

I would like to have a code multiplying by 100000000 in case of M (million), and 100000000000 in case of B (billion).

How can I do it?
Thanks a lot

Bruno Lelli

[brunolelli87]

Hello,

Thanks for your quick response...
I'm sorry but it's not working, it's saying that there is a missing FROM...

Thanks

[Taoufiq_Zarra · Accepted solution]

and ?

Data:

load pick(wildmatch(A,'*M','*B'),Num(purgechar(A,'M.')*100000000,'USD #.##0,00'),Num(purgechar(A,'B.')*100000000000,'USD #.##0,00')) as New_A,* inline [
A
5.122,88 M
1.2 B
] (delimiter is '|') ;

[brunolelli87]

It's working perfectly now!

Thank a lot