Using FOR/NEXT around SUBFIELD

[#1]

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!

[sunny_talwar · Accepted solution]

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

[#3]

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!