topic Re: Converting qlikview text field to numeric field in QlikView

Converting qlikview text field to numeric field

edwinmatlapeng — Sun, 23 Jun 2013 16:24:52 GMT

Hi All,

I run scripts to load QVD's from text files. Problem is my money fields appear and behave as text fields. for example, on a sum function
I get null values and when I put them on the sheet as list boxes you can see the numbers with leadting zeroes! I have already tried the Num(Amount), Num#(Amount,'#'), num(Amount) and the Amount field would not yield.

Please help.

Re: Converting qlikview text field to numeric field

swuehl — Sun, 23 Jun 2013 17:30:19 GMT

An number interpretation function like num#() should work.

What's the exact format of your text field values? Are there any currency symbols or something like that?

Decimal or thousand separator different from your default ones?

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Sun, 23 Jun 2013 17:37:12 GMT

Using a QVD viewer, the field has leading zero's and the amounts. I dont understand why qlikview interpret the field as a text field. May default money mask is SET MoneyFormat=' # ,##0.00;-# ,##0.00'; Please help me out!

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Sun, 23 Jun 2013 17:44:17 GMT

The currency symbl is 'R'

Re: Converting qlikview text field to numeric field

swuehl — Sun, 23 Jun 2013 18:07:39 GMT

Could you post one (or part) of your source text files? If 'R' is part of your text value, I believe you should add it to the format code string.

Re: Converting qlikview text field to numeric field

Sokkorn — Mon, 24 Jun 2013 00:55:52 GMT

Hi Edwin,

Suppose that your money field have this format 'R #,##0.00; R -#,##0.00'

When load data you need to use

[Data]:

LOAD

Field1,

Money(Num#(MoneyField,'R #,##0.00; R -#,##0.00')) AS [MoneyField],

...

From Table

Regards,

Sokkorn

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Mon, 24 Jun 2013 04:04:23 GMT

R is not part of the input value.

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Mon, 24 Jun 2013 04:07:16 GMT

Hi Sokkorn,

Thank you for your response. When I use more than one format the number disappears altogether and behaves like a null! But using Num# or Money# individually the field still appears but act as a text field!

Re: Converting qlikview text field to numeric field

Sokkorn — Mon, 24 Jun 2013 04:09:33 GMT

Hi Edwin,

Can you provide me full format of your field? Just give me one example of your field then we look into it together.

Regards,

Sokkorn

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Mon, 24 Jun 2013 04:28:31 GMT

Finally got the pic on. The two columns on the list boxes are the amounts I want. The rightmost is a chart on which I am summing the Credit column by the Functional Area. This is what I am getting.

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Mon, 24 Jun 2013 04:35:52 GMT

Strange thing is when I use Num without the hash, the values disappear!

Re: Converting qlikview text field to numeric field

Sokkorn — Mon, 24 Jun 2013 05:07:25 GMT

Hi Edwin,

Try this in your load script

Mid(Credit, FindOneOf(Credit, '123456789')) AS [Credit],

....

Regards,

Sokkorn

Re: Converting qlikview text field to numeric field

Sokkorn — Mon, 24 Jun 2013 05:09:58 GMT

Or REPLACE(LTRIM(REPLACE(Credit, '0', ' ')), ' ', '0') as Credit

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Mon, 24 Jun 2013 05:23:07 GMT

Sokkorn, man! Thank you so so so much. Been awake all night! Thank you!!

Re: Converting qlikview text field to numeric field

edwinmatlapeng — Mon, 24 Jun 2013 05:31:24 GMT

this solution works as well! Thank you, man!