4 Replies Latest reply: Oct 18, 2013 5:00 PM by m w RSS

    Text equality issue

      I have a couple tables of parts that I'm trying to compare (which parts are on which list or both, missing data on one table that is populated in another, etc). In doing so, I came across something disconcerting with Qlikview. Try the following script in a text box:

       

      =if('9108E03'='9108D03',1,0)

       

      This should return 0 as the part numbers are different ('E' <> 'D'), but returns 1 instead. This also occurs  if I use a "Group by PartID" statement in load statements, matching the 2 different IDs as the same, and even going so far as to tell me "9108DO3,9108DO3" for the result of

       

      concat(PartID,',') as AllPartIDs

       

      as my aggregate statement in the load script.

       

      Hopefully I'm doing something wrong, but is there a way to make these two values not match?

       

      I've attached a QVW showing the issue.

        • Re: Text equality issue
          m w

          Try:

          =if(text('9108E03')=text('9108D03'),1,0)

            • Re: Text equality issue

              That helps. 2 questions then:

               

              1.      How do I use a “Group by” aggregation in the load script for these values (Group By Text(PartID) errors out)?

               

              2.      And more out of curiosity, why do these two values equate, but ‘9108F03’ is considered different?

               

              Thanks for the quick response,

              ~Zack

              • Re: Text equality issue

                After some fiddling around, it's possible to accomplish a "Group By" by loading the data initially as Text(PartID), and then doing a separate aggregate load statement.

                 

                Still don't know why '9108F03' is different but the others are considered equal, but the problem is solved. Thanks Michael

                  • Re: Text equality issue
                    m w

                    From 11.2 SR4 Release Notes:

                     

                    Text fields with values like 1E4 or 1D4 could be misinterpreted. When loading text fields QlikView has always tried to interpret numeric values and load them as numeric, not text. Due to two different bugs the behaviour of QlikViews interpretation of exponential numbers, nnnEn or nnnDn, has changed between releases. The safest way to make sure you get the interpretation you like has always been to cast the specific fields to the right type in the script.