4 Replies Latest reply: Jul 23, 2014 9:51 AM by Bill Markham RSS

    Qlikview equivalent  of over partition

      Hello,

      I'm searching for the equivalent of this SQL code in Qlikview:

      select  ID,CODE, COUNT(*) OVER (PARTITION BY ID) as count_ID from TABLE group by 1,2

       

      Thank you in advance.

      Issam

        • Re: Qlikview equivalent  of over partition
          Dave Riley

          Hi Issam,

           

          If the direct sql code isn't working then I think the only way in Qlikview script is to left join itself ...

           

          tmp:

          Load *;

          select ID, CODE from TABLE group by 1,2;  //sql code

           

          left join

          load ID, count(ID) as count_ID resident tmp group by ID;

           

           

          flipside

          • Re: Qlikview equivalent  of over partition
            Fernando Keuroglian

            Hi

            Load *;

            sql select

            ID,CODE, COUNT(*) OVER (PARTITION BY ID) as count_ID

            from TABLE

            group by 1,2;


            good luck

            • Re: Qlikview equivalent  of over partition

              thank you for your response,

              Actually I  have a table stored in a qvd  like below:

              ID      Code   Info1  Info2   Info3   ……

              01      A        

              02      B

              05      C

              07     X

              05     Y

              10     D

              02    F

               

              and i want to add a new field (Count_ID) in the script which indicate the ID present more than twice.(like below)

               

              ID      Code    Count_ID   Info1  Info2   Info3   ……

              01      A          1

              02      B          2

              05      C          2

              07      X           1

              05      Y          2

              10      D          1

              02      F           2

               

              Regards,

              Issam