4 Replies Latest reply: Oct 16, 2015 10:56 AM by Enrique Herranz RSS

    Exists/ not Exists using flags

    Enrique Herranz

      Hi everyone,

      I wondered if someone could give me some help here. I am struggling with something that I thought would be straight forward , but for whatever reason I don´t get what I need. I am trying to compare two lists of items from two files ( rev A and rev B files ) and detect which items from list A are missing in list B and viceversa ( basically I need to find items removed and items added in list B ) and create a field that I can use to select 'Added Items' or 'Removed Items'. I thought a very quick and simple way would be to add some flags to the script and use a rangesum function ( to "sum" the two columns, Flag_A + Flag_B ) and create the field I need ( what I called Flag_delta ).

      This new field should have 3 values : -1,0,1 ( -1 for items existing only in list A , 0 for items existing in both lists , 1 for items existing only in list B )

      See my script below :

      Flags.png

      For whatever reason the RANGESUM function is not giving me the value 0 , only values -1 and 1 . Do you know what I am doing wrong here ?

      Any help is appreciated

      Many thanks

      Enrique

        • Re: Exists/ not Exists using flags
          Gysbert Wassenaar

          rangesum sums the values of the flags of the same record, not across records. What you want is

           

          Delta:

          LOAD [Equipement TAG],

               sum(Flag_A),

               sum(Flag_B)

               sum(rangesum(Flag_A, Flag_B))

          Resident Temp

          Group By [Equipement TAG];

            • Re: Exists/ not Exists using flags
              Enrique Herranz

              Many thanks Gysbert ! it works great now. It also helps me to detect some problems with my source data files as it seems there are some duplications there ( you can see numbers lower than -1 or higher than 1 . There is something though I find strange and it happens to me as well in other Qview apps ( I am a beginner :-) , in the list box ( Equipment TAG ) the first value on the list is a blank value ( Null ? ) . Do you know why I have a blank value there ? Could it be a problem coming from my source file ( excel )?

              Thanks again for the help

              Rgds

              Enrique

               

              Flags_tables.png