3 Replies Latest reply: Sep 2, 2015 3:37 PM by Massimo Grossi RSS

    Loop skips the duplicate line

    P Q

      Hi QV gurus,

       

      I have a problem with looping: when a field has duplicate, the loop skips the duplicate line. For example:

       

      TABLE:
      load * inline
      [A, B, C
      China, Cotton, Shirt
      India, Cotton, Socks
      Japan, Leather, Jacket]
      ;

      let vNOR=NoOfRows('TABLE');
      for i=1 to $(vNOR)
      let vA=FieldValue('A',$(i));
      let vB=FieldValue('B',$(i));
      let vC=FieldValue('C',$(i));

      trace $(vA);
      trace $(vB);
      trace $(vC);

      NEXT;

       

      You can see in column B, line 1 and line 2 has duplicate values. When I run the script, the log is as below:

       

      TABLE << INLFFD1 3 lines fetched
      China
      Cotton
      Shirt
      India
      Leather
      Socks
      Japan

       

      Jacket

       

      The record "India-Cotton-Socks" became "India-Leather-Socks" while for Japan, the column B is missing. So I guess the loop skips the duplicates in column B, thus causing a problem with the data.

       

      Could you kindly teach me how to fix this?

       

      BR, PQ

        • Re: Loop skips the duplicate line
          Fernando Tonial

          Hi, You can try this.

           

          You have use a function Peek() insted FieldValue().

           

          TABLE:
          load * inline 
          [A, B, C
          China, Cotton, Shirt
          India, Cotton, Socks
          Japan, Leather, Jacket];
          
          
          let vNOR=NoOfRows('TABLE');
          for i=1 to $(vNOR)
          let vA=Peek('A',$(i)-1);
          let vB=Peek('B',$(i)-1);
          let vC=Peek('C',$(i)-1);
          
          
          trace $(vA);
          trace $(vB);
          trace $(vC);
          
          
          NEXT;
          

           

          Best Regards.

          Don't Worry, be Qlik.

          Tonial.

          • Re: Loop skips the duplicate line
            Sasidhar Parupudi

            this is the help text

            FieldValue(fieldname , n )

            Returns the field value found in position n of the field fieldname (by load order). fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes. The first field value is returned for n=1. If n is larger than the number of field values, NULL is returned.

            Note: This function will only work with distinct field values.


            use Peek

            TABLE:

            load * inline

            [A, B, C

            China, Cotton, Shirt

            India, Cotton, Socks

            Japan, Leather, Jacket];

             

            let vNOR=NoOfRows('TABLE');

            for i=0 to $(vNOR)-1

            let vA=Peek('A',$(i));

            let vB=Peek('B',$(i));

            let vC=Peek('C',$(i));

             

            trace $(vA);

            trace $(vB);

            trace $(vC);

             

            NEXT;

            • Re: Loop skips the duplicate line
              Massimo Grossi

              TABLE:

              load * inline

              [A, B, C

              China, Cotton, Shirt

              India, Cotton, Socks

              Japan, Leather, Jacket];

               

              for i=0 to NoOfRows('TABLE')-1

                let vA=Peek('A',$(i));

                let vB=Peek('B',$(i));

                let vC=Peek('C',$(i));

                trace $(vA) $(vB) $(vC);

              NEXT;

               

               

              TABLE << INLE08 3 lines fetched

              China Cotton Shirt

              India Cotton Socks

              Japan Leather Jacket