Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
GP
Contributor II
Contributor II

Calculation resulting in a '-'

I am doing some very simple calculations on load, but rather than a number the result is a '-' but this only occurs on the numbers generated from an applied map field where I substitute a number where none is available from the mapping table.
Would be most grateful if you can shed any light on why this may be.... Simple load script calculationsSimple load script calculationsDiffering result from same calcDiffering result from same calc

Labels (1)
2 Solutions

Accepted Solutions
Mauritz_SA
Partner
Partner

Hi there GP

While you are clutching at straws I though I might make a simple suggestion. Seeing as the following all evaluate to a "-":

  • Num(Text(2)/2)
  • Num(Text(2))/2
  • Num#(Text(2)/2)

But Num#(Text(2))/2 evaluates to 1. Perhaps try using If(Pages < 3, Num(1), Num#(Pages)/2 AS totPages in the Jobs preceding load. It actually seems as if the Mapping table might be returning a string.

clipboard_image_0.png

You can obviously try and make the values in the mapping table numbers (instead) of strings as well, but the suggestion above should easily show you whether it is in fact being seen as a string.

Good luck!

View solution in original post

rwunderlich
MVP & Luminary
MVP & Luminary

I believe as @Mauritz_SA suggested, that your mapping table values are strings.  Try wrapping the value in Num() when you load the mapping table. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

6 Replies
treysmithdev
Luminary
Luminary

This does seem to be strange behavior. 

I would suggest two things:

  1. Check the field metrics in the data model viewer for Pages and totPages
    1. Are there any NULL values?
    2. Are there any weird data type flags?
  2. Replicate the expressions in the script in a table chart. See if it breaks there as well.
Blog: WhereClause   Twitter: @treysmithdev
GP
Contributor II
Contributor II

I've tried various things, the 'totPages' column was to see what happened when I divide 'pages' by 2 and that is where it is failing, even though the pages column seems to be populated with correct numbers.

I've also tried loading Num(Pages) and all other figures involved defined as numbers.

There are no NULL values but the mapping table doesn't have a value for all entries. Here's how it's mapped.

Screen Shot 2019-10-02 at 17.25.28.png

Now clutching at straws...

 

Mauritz_SA
Partner
Partner

Hi there GP

While you are clutching at straws I though I might make a simple suggestion. Seeing as the following all evaluate to a "-":

  • Num(Text(2)/2)
  • Num(Text(2))/2
  • Num#(Text(2)/2)

But Num#(Text(2))/2 evaluates to 1. Perhaps try using If(Pages < 3, Num(1), Num#(Pages)/2 AS totPages in the Jobs preceding load. It actually seems as if the Mapping table might be returning a string.

clipboard_image_0.png

You can obviously try and make the values in the mapping table numbers (instead) of strings as well, but the suggestion above should easily show you whether it is in fact being seen as a string.

Good luck!

View solution in original post

rwunderlich
MVP & Luminary
MVP & Luminary

I believe as @Mauritz_SA suggested, that your mapping table values are strings.  Try wrapping the value in Num() when you load the mapping table. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

GP
Contributor II
Contributor II

This seems to have done the trick, the mapping table was evaluating as text, I've attached a screen shot of what worked. Appreciate your help.

Many thanks

Screenshot 2019-10-03 at 08.58.57.png

GP
Contributor II
Contributor II

Actually this didn't fix it... I just ended up with a column containing the expression as text e.g. "Num#(2)" - but I think that this may be data related rather than calcuations at fault...