12 Replies Latest reply: Apr 4, 2018 8:31 PM by Sunny Talwar RSS

    Set Analysis Problem

    Eduardo DImperio

      Hi,

       

      i have a set analysis that not work. Whats wrong please?

       

      count({<DIA0=-{}>} distinct OID_METER) Not recognize Null

       

      IF (ISNULL(DIA0),'EMPTY') Recognize Null

       

       

      set_analysis.JPG

        • Re: Set Analysis Problem
          Sunny Talwar

          Null value cannot be compared to anything in set analysis... why don't you try this

           

          Count({<DIA0 = {"*"}>} DISTINCT OID_METER)

            • Re: Set Analysis Problem
              Eduardo DImperio

              Hi Sunny, that a little strange, because i have been used this code for a while, but in my new version of an app it not working anymore. But i need to count OID_METER where DIA0 are null. Count({<DIA0 = {"*"}>} DISTINCT OID_METER) doesn't work

                • Re: Set Analysis Problem
                  Sunny Talwar

                  How about this

                   

                  Count({<OID_METER = {"=Len(Trim(DIA0)) = 0"}>} DISTINCT OID_METER)

                    • Re: Set Analysis Problem
                      Eduardo DImperio

                      Sunny, that works with a small change, its not = that i need, its <>.

                      Count({<OID_METER = {"=Len(Trim(DIA0)) <> 0"}>} DISTINCT OID_METER)

                       

                      So, about that, why doesn't work count({<DIA0= - {}>} distinct OID_METER) ?

                        • Re: Set Analysis Problem
                          Sunny Talwar

                          I thought you said you wanted to count OID_METER where DIA0 is null?

                           

                          Capture.PNG

                            • Re: Set Analysis Problem
                              Eduardo DImperio

                              Sorry about that, my mistake, but you know why Count({<DIA0= - {}>} distinct OID_METER)  doesn't work?

                                • Re: Set Analysis Problem
                                  Sasidhar Parupudi

                                  i think it is because your data is having nulls and the empty set is not going to filter the nulls..

                                  When you use len in conjunction with trim, it is going to eliminate both nulls and empty records

                                  Try the following and if it works as intended then the above statment should be correct

                                  Count({$< DIA0 -= {'=IsNull(DIA0)=-1'} >}  DISTINCT  OID_METER )

                                  • Re: Set Analysis Problem
                                    Sunny Talwar

                                    In my very first response I mentioned this

                                     

                                    Capture.PNG

                                     

                                    What this means is that if a row has DIA0 as null, set analysis won't be able to put any filtration on that specific row based on DIA0... example

                                     

                                    OID_METER          DIA0

                                    1                              4

                                    2                              Null()

                                    3                              2

                                    4                              4

                                    5                              Null()

                                     

                                    When you will use any set analysis on DIA0, consider it to start looking at 1, 3, & 4 rows only because the other two DIA0 are null....

                                     

                                    But by using a set analysis on OID_METER, you can check if the Len(Trim(DIA0)) = 0 or not... if it is then count DIA0, else not..... But this can only work when there is a one to one relation between the ID field and DIA0... what this means is... if for example you have this


                                    OID_METER          DIA0

                                    1                              4

                                    2                              Null()

                                    3                              2

                                    4                              4

                                    5                              Null()

                                    5                              4


                                    It might not work the way you would want.... If you would want it to still count OID_METER 5, then it may not because there is another OID_METER 5 where DIA0 is not null....

                                     

                                    I hope this helps.

                                     

                                    Best,

                                    Sunny

                            • Re: Set Analysis Problem
                              Krishna Nagulapally

                              u can try all- 

                               

                              1.   IF (ISNULL(DIA0), Count(distinct OID_METER) )

                              2.   Count( distinct   If(DIA0 = null() ,OID_METER ))

                              3.   Count({$<DIA0 ={'*'}-{''}>} OID_METER)   -  this expression counts non-null values

                              4.    Count({$< DIA0 = {"=Len(Trim(DIA0))=0"} >}  DISTINCT  OID_METER )   - Set expression that counts     distinct  OID_METER  where DIA0 is null


                              PS - i didnt test these expressions.