topic Re: How to convert values from "£#m" to "£#,###,###" & "£##k" to "£##,###" in QlikView

How to convert values from "£#m" to "£#,###,###" & "£##k" to "£##,###"

Wed, 25 Nov 2020 16:16:04 GMT

Hello Community.

I am working with some data at the moment which has 'TransferFee' values stored as £5.50m, £45k, £560k, £55.69m, £2m etc, and I'd like to understand the process of how to convert these values into Standard numbers e.g £5.50m to £5,500,000, £40k to £40,000.. As the data is currently stored in a text format (as it contains M's and K's), I'm unsure of what to do.

I imagine that I need to manipulate the data in the script during the loading phase ?

Any help will be appreciated

Thanks

Liam

Re: How to convert values from "£#m" to "£#,###,###" & "£##k" to "£##,###"

m_woolf — Wed, 15 Mar 2017 16:49:51 GMT

if(right(TransferFee,1) = 'm', purgechar(TranferFee,'£m')*1000000,

if(right(TransferFee,1) = 'k', purgechar(TranferFee,'£m')*1000)) as TransferAmt;

This should result in the correct value without any currency symbol included.

You may need to wrap in num().

Re: How to convert values from "£#m" to "£#,###,###" & "£##k" to "£##,###"

Wed, 15 Mar 2017 21:56:54 GMT

Thank you very much for your swift response, this worked a treat !!!