Field Format and Calculation Problem ?

[#1]

Hi;

0.18
0.25
1.00
1.05
1.06
1.15
1.32
1.33
1.46
1.67
1.75
2.00
2.06

I have 2 fields that comes from separate tables from the Oracle DB and these are below and above there.
I would like to multiply this 2 fields and show this calculation in another listbox.

But i could not change fields format so my expression does not work !!!

ex: (fieldA * fieldB)

How can i do it ?

Thanks in advance...

1/1

1/2

1/3

1/4

1/5

1/6

1/7

1/8

1/9

1/10

1/11

1/12

1/13

1/14

1/16

1/17

1/18

1/19

1/20

[#2]

Try:

(fieldA * Num(fieldB))

QlikView is probably treating the fractions as text (or worse, dates). If you use Num() it will evaluate the fraction to a decimal. It may even be a good idea to use the Num() function in your load.

LOAD Num(fieldB) As fieldB
FROM...

[johnw]

NMiller wrote:If you use Num() it will evaluate the fraction to a decimal.

Wait, it does? It does! Clever. I had no idea. 🙂

[#4]

Thanks for responses

But it does not work !!!

Any idea ???


Thanks in advance

[#5 · Accepted solution]

Sorry, I must have spoken too soon. It seems to work in a text box as Num(1/3), but not when the data is a fraction.

You can use Evaluate(FIELD) in your load and it seems to work. Using Evaluate(FIELD) in an expression does not work though.

EDIT: Maybe because Evaluate is not valid in an expression, only in the load. I attached an example that loads the Fraction and then Evaluates that fraction into a decimal value.

Last Edit, I Promise: I figured out an expression that would do it. It's not pretty, so I would suggest the Evaluate option, but this will probably work fine:

Left(Fraction, Index(Fraction, '/') - 1) /
Mid(Fraction, Index(Fraction, '/') + 1)

[johnw]

I agree that it's probably best to do it in the load, but here's a slightly simpler chart expression that works:

subfield(Fraction,'/',1)/subfield(Fraction,'/',2)

And num(Fraction) not working makes sense to me in hindsight. When you say num(1/3), it's almost certainly just doing a straight division, and only THEN evaluating the num() function. Standard order of precedence stuff, working from inside out, nothing clever. If you do num('1/3'), then it doesn't work, I think indicating that the num() function doesn't actually know what to do with fractions.

As an aside, I'd really like for the evaluate() function to work in chart expressions as well.

[#7]

Subfield, yes, much nicer. I'll have to add that one to my cheat sheet, it can really come in handy when working on fields that aren't necessarily delimited lists. Thanks for the tip!

[#8]

THANKS FOR RESPONSES...


HAVE A GOOD TIME...