Wednesday, April 5, 2017

Converting Edited Number Text into a Numeric Field

My co-worker, Naveen, had a requirement for a document that would load some financial data from a file that a client had sent to us. The client had created the file with a “screen scraping” type of application that captured the number fields as edited numeric text. For example, a field might contain  ($3,046.10)   So, that example includes a dollar sign, comma as a thousands separator, period as a decimal point, and parenthesis to indicate a negative number or credit.

Naveen needed to load the data as a number. Here’s how it was done:
We used two functions. A Num# function converted the text string into a dual field containing both a text portion and a numeric portion. Then, an outer Num function extracted just the numeric portion. For example, one of the number fields was named AUG. In the loadscript, the line that converted the edited number into a simple numeric field looked like this:
Num(Num#(AUG, '$#,##0;($#,##0)'))

The edit string or format code, the part within the single quotes, can vary depending on your requirement. The edit string I used in the example is a good one for the kind of editing you might find in a financial spreadsheet.

Here's a few other examples of edited number text and how it looks after using this expression:
AUG
Num(Num#(AUGUST,'$#,##0;($#,##0)'))
$0
0
$1
1
$1,251
1251
$1,465
1465
$1,466
1466
$3,724
3724
$4,158
4158
($11)
-11
($132)
-132
($2)
-2
($4)
-4
What now?
         (this is a null)
19
19
25.12
25.12
25.1234
25.1234
25.3
25.3
2,070
2070


  ★★★

4 comments:

Anonymous said...

Hi there,

In this bit:

'$#,##0;($#,##0)'

What's the difference between the formatting before the semi-colon (;) and after it?

Are you saying take the $#,##0 from the ($#,##0)?

-TB said...

The part after the semi-colon tells how to format the number if it is a negative number. You can see where I borrowed that format code if you look at the chart properties - Number tab and look at a field where the Money button is checked - the Number tab shows a format code and I just copied that one. I removed the '.00' part to make it a little more general purpose.

For more information about this topic search QlikView Help for format codes. (I called it an edit string in my blog post but the Help for the Num function calls it a format code.)

Thanks for reading the blog!
-Tim

Senarath Bandara Herath said...

Hi,
what if there are multiple currency codes such as $-USD and GBP in values ?

Thanks
Senarath

-TB said...

Hi Senerath,
Could you post an example of text(s) like that? I've never encountered it.

If you have a field containing values with various currencies then simply removing the currency symbol is only part of the solution. You would have to store or keep track of what currency the original value contained, or, make it part of a conversion to some "standard" currency.

You could try something like this:
if index(myfield, '$')>0 then
/*remove dollar*/
else
/*remove other*/
end if

Regards,
-Tim