4 Replies Latest reply: Dec 20, 2016 8:04 AM by surendra j RSS

    Distinct not working

    Eduardo DImperio

      Hi everyone,

       

      I really dont get it why my distinct and my not exist doesnt work.

       

      When i use distinct (image1), im still seeing two value 87066, and when i use not exists (image 2), i still have 87066 value.

       

      Why?

       

       

      Exclusao:

      LOAD

      DISTINCT OID_METER,

                EXCLUDE_METER

      RESIDENT ANALISE_TMP

      WHERE NOT EXISTS (OID_METER,EXCLUDE_METER);

       

      Distinct Error.JPGNot Exists.JPG

        • Re: Distinct not working
          surendra j

          Hi

           

          please check how these values are internally stored in qlikview either in text or number format.

           

          --surendra

          • Re: Distinct not working
            Eduardo DImperio

            The not Exists i found the error, i change the order of function

             

            WHERE NOT EXISTS (OID_METER,EXCLUDE_METER);

            WHERE NOT EXISTS (EXCLUDE_METER,OID_METER);


            But still don't get it the distinct

            • Re: Distinct not working
              Jonathan Dienst

              Its not completely clear what you are trying to do, but the script that you provided loaded the first line because the first record is distinct by definition and nothing exists yet so the where clause will be true. The second line is also distinct (because distinct in this context means a distinct line, all the records), and will get loaded because EXCLUDE_METER is null, so the exists evaluates to false and the where clause will be WHERE NOT FALSE (always true).

               

              Perhaps a clear explanation of what you are trying to do would help.

                • Re: Distinct not working
                  Eduardo DImperio

                  Hi Jonathan, you are right, now i understant the concept.

                  I'll explain using a example of another discuss:

                  I have some meters and they register water consume every hour. If some meter register some zero value, i need to show no more that meter, even if this meter register some value after that zero. like this example below:

                  Input

                  Meter  Value Hour

                  123      10    1:00am

                  123      15    2:00am

                  123      00    3:00am

                  123      15    4:00am

                  345      12    1:00am

                  345      17    2:00am

                  345      05    3:00am

                  345      30    4:00am


                  Output

                  Meter  Value Hour

                  345      12    1:00am

                  345      17    2:00am

                  345      05    3:00am

                  345      30    4:00am

                   

                  I try use this code, that seeing very simple, but for some reason that no one knows, that crash my QS.

                   

                  ANALISE:

                  LOAD

                    OID_METER,

                      ID_LEITURA,

                      NAME_SYSTEM,

                      NAME_GROUP,

                      NEIGHB_SYSTEM,

                      CITY_SYSTEM,

                      NAME_TYPE_METER,

                      TYPE_CONSTANT,

                      NAME_UC,

                      ADDRESS_BLOCK_UC,

                      DATE_READ,

                      VALUE_READ,

                      CONSUMODIA0,

                      CONSUMODIA1,

                      CONSUMODIA2,

                      HOUR(HORA) as HORA_2,

                      EXCLUDE_METER

                      RESIDENT ANALISE_TMP

                         WHERE NOT EXISTS (EXCLUDE_METER,OID_METER)

                      ORDER BY OID_METER,HORA DESC;

                   

                  So now im use a short version, to get this OID_Meters where not in Exclude_oid meters and i'll try a inner join with analise after that, just because the code above is crashing for no reason.