topic Field Format and Calculation Problem ? in QlikView

Field Format and Calculation Problem ?

Tue, 05 Oct 2010 15:21:54 GMT

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

Field Format and Calculation Problem ?

Tue, 05 Oct 2010 15:48:27 GMT

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...

Field Format and Calculation Problem ?

johnw — Tue, 05 Oct 2010 22:22:50 GMT


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

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

Field Format and Calculation Problem ?

Wed, 06 Oct 2010 08:10:16 GMT

Thanks for responses

But it does not work !!!

Any idea ???

Thanks in advance

Field Format and Calculation Problem ?

Wed, 06 Oct 2010 14:56:41 GMT

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)

Field Format and Calculation Problem ?

johnw — Wed, 06 Oct 2010 21:06:34 GMT

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.

Field Format and Calculation Problem ?

Wed, 06 Oct 2010 22:07:57 GMT

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!

Field Format and Calculation Problem ?

Thu, 07 Oct 2010 13:11:57 GMT

THANKS FOR RESPONSES...

HAVE A GOOD TIME...