Converting qlikview text field to numeric field

[edwinmatlapeng]

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.

[swuehl]

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?

[edwinmatlapeng]

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!

[edwinmatlapeng]

The currency symbl is 'R'

[swuehl]

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.

[Sokkorn]

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

[edwinmatlapeng]

R is not part of the input value.

[edwinmatlapeng]

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!

[Sokkorn]

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

[edwinmatlapeng]

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.