2 Replies Latest reply: Sep 28, 2015 6:45 PM by Mark Ford RSS

    Using FOR/NEXT around SUBFIELD

      I have a field in an Excel sheet that can have up to 200 values in the field separated by a |.  I want to split out the values using SUBFIELD.  I can do it by having a separate SUBFIELD statement for each one but that's 200 rows so I'd like a short way to do it using a FOR/NEXT loop but I can't get it to accept it.

       

      I tried:

       

      [membership_tmp]:

      LOAD Web_Site_Member_ID as tmp1_mbr_id,

      FOR INDX = 1 TO 199

        SUBFIELD(Products_Used, '|', $(INDX))  as tmp1_product_$(INDX),

      NEXT INDX

          SUBFIELD(Products_Used, '|', 200)  as tmp1_product_200

      RESIDENT Raw_Member_Data;

       

      but it gives me a syntax error on INDX =.

       

      Can this be done?  Thanks!

        • Re: Using FOR/NEXT around SUBFIELD
          Sunny Talwar

          Try it without any number and let QlikView handle it by putting it in a different row for you:

           

          [membership_tmp]:

          LOAD Web_Site_Member_ID as tmp1_mbr_id,

            SubField(Products_Used, '|')  as tmp1_product

          RESIDENT Raw_Member_Data;

           

          And I guess if you want it all in different columns, manipulate the script after you run the above. If you can provide some sample data, we might be able to give a better response.

           

          HTH

           

          Best,

          Sunny

            • Re: Using FOR/NEXT around SUBFIELD

              Wow, I was really over engineering a simple solution!  I had as my next step a FOR/NEXT loop that took the results of my SUBFIELD step and created a new row for each column so all values were in the same column on a separate row.  But now I see that Qlik Sense will do that automatically if I just leave off the column number on the SUBFIELD statement.  Much cleaner!  Thanks for the tip!