- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Field Format and Calculation Problem ?
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
NMiller wrote:If you use Num() it will evaluate the fraction to a decimal.
Wait, it does? It does! Clever. I had no idea. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for responses
But it does not work !!!
Any idea ???
Thanks in advance
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
THANKS FOR RESPONSES...
HAVE A GOOD TIME...