Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

Converting from text to number with abbreviation!

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

1 Solution

Accepted Solutions
Taoufiq_Zarra

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 '|') ;
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

6 Replies
Lisa_P
Employee
Employee

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 :

Capture.PNG

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
brunolelli87
Creator II
Creator II
Author

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

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

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 '|') ;
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
brunolelli87
Creator II
Creator II
Author

It's working perfectly now!

Thank a lot