3 Replies Latest reply: Feb 4, 2013 3:10 PM by Eduardo Correa da Silva RSS

    Help with Where clause

      Hello,

       

      I am trying to apply a filter like this:

       

      I have the NumYearMonth field wich has numeric values eg: 1,10,15,etc, etc

      I only need to retrieve one value and that is the penultimate, next to last, before to last one, secont last, however you want to call it.

      The field should always contain a sequencial numeric value, so for instance if the highest number is 30, I only want the 29 value to be loaded.

      The numbers may change reload after reload so I need something dinamic.

       

      I was thinking something like

      WHERE NumYearMonth = Max(NumYearMonth) -1

       

      I have the below script;

       

      QUALIFY *;

      Chart3:

      LOAD

      AuditPhase,

      AuditPhaseID,

      [Region Only],

      NumYearMonth,

      [DDQ Submit]

      Resident TABLE1

      Where NumYearMonth = Max(NumYearMonth) -1 and

      ([AuditPhase]='DDQ' or [AuditPhase]='OSI' or [AuditPhase]='EDD');

       

      But QlikView doesn't like that.

       

      Any ideas how can I accomplish this?

        • Re: Help with Where clause
          Alessandro Saccone

          Use the same table,

           

          Second:

          LOAD

               Max(NumYearMonth) -1 as max_minus_1

          resident "Tablename"

          group by KEY

           

          so you compute the max value minus 1 grouped by key fields, then you have to load first table

          and do left join with the second one "Second"

           

          It have to work.

           

          Hope it helps

            • Re: Help with Where clause

              Hello, use the idea of Alexandros17.

               

              Once you create the auxiliary table, use the PEEK function to return the most value and use it in their condition.

               

              Ex.:

               

              QUALIFY *;

              Chart3:

              LOAD

                   AuditPhase,

                   AuditPhaseID,

                   [Region Only],

                   NumYearMonth,

                   [DDQ Submit]

              Resident TABLE1;

               

              Second:

              LOAD

              MAX(NumYearMonth) -1 as max_minus_1

              resident Chart3;

               

              Result:

              LOAD

                   AuditPhase,

                   AuditPhaseID,

                   [Region Only],

                   NumYearMonth,

                   [DDQ Submit]

              Resident Chart3

              WHERE NumYearMonth = PEEK('max_minus_1') and

              ([AuditPhase]='DDQ' or [AuditPhase]='OSI' or [AuditPhase]='EDD');

            • Re: Help with Where clause
              khadar basha

              Hi,

               

                 can you please provide a test data.

               

               

              Regards