# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for
Did you mean:
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.

Best regards

1 Solution

Accepted Solutions

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") 😉
6 Replies
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:
Abb, Mult
M, 1000000
B, 1000000000
];

[Table]:
[
Num
5.122 M
1.2 B
];

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

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,``

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 :

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
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

Creator II
Author

Hello,

I'm sorry but it's not working, it's saying that there is a missing FROM...

Thanks

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") 😉
Creator II
Author

It's working perfectly now!

Thank a lot

Community Browser