topic Re: Converting from text to number with abbreviation! in QlikView

Converting from text to number with abbreviation!

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Converting from text to number with abbreviation!

Lisa_P — Tue, 28 Apr 2020 00:32:22 GMT

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;

Re: Converting from text to number with abbreviation!

Taoufiq_Zarra — Tue, 28 Apr 2020 00:32:53 GMT

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 :

Re: Converting from text to number with abbreviation!

brunolelli87 — Tue, 28 Apr 2020 03:04:32 GMT

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

Re: Converting from text to number with abbreviation!

brunolelli87 — Tue, 28 Apr 2020 03:10:46 GMT

Hello,

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

Thanks

Re: Converting from text to number with abbreviation!

Taoufiq_Zarra — Tue, 28 Apr 2020 03:38:45 GMT

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

Re: Converting from text to number with abbreviation!

brunolelli87 — Tue, 28 Apr 2020 16:42:51 GMT

It's working perfectly now!

Thank a lot