2 Replies Latest reply: Sep 22, 2014 7:57 AM by Valeriy Shylin RSS

    If(Missing = Missing, 1, 2) always yields Null

    Valeriy Shylin

      I noticed a difference in how different versions of Qlikview manage one specific case with calculated dimensions.

      And QV 11.0 does it better than 11.2.

       

      I have 3 tables: Fact, User, SpecialUserMark. My chart counts unique identifiers in Fact. But the dimension is based on Users.

      In particular, I want to count how many records in Fact belong to Normal and Special users.

       

      So, the calculated dimension is the following:

      IF(User.UserValue = SpecialUser.SpecialValue,'Special User', 'Normal User')

       

      The expression is the following:

      count(DISTINCT FactTable.DistinctID)

       

      My problem is that some Fact table rows don't have the corresponding user. So, the dimension formula translates more or less in IF(Missing = Missing, 'something', 'something else').

       

      Below is how QV 11.0 (11.00.11282.0 SR1 64-bit Edition) displays the table:

      QV_11.0.png

      Naturally, the missing users (UserId = k) are treated as "something else" => "Normal User".

       

      Below is how QV 11.2 (11.20.11922.0 SR2 64-bit Edition) displays the same table:

      QV_11.2.png

      The missing users are treated as NULL (I tried changing the NULL symbol representation). The whole formula gets invalidated. So, wrapping it inside IF(ISNULL(IF(missing=missing,...,...)),...,...) does not work as it keeps always yielding NULL.

       

      Find attached the example.

       

      Any ideas on how to solve this problem?

       

      The only solution I came up with is to create "fake" records in the User table, which makes it enough for the formula to get evaluated correctly.

      But I have too many similar problems in the same dashboard and these "fake" records eventually affect calculations in other charts.