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.
LOAD Web_Site_Member_ID as tmp1_mbr_id,
FOR INDX = 1 TO 199
SUBFIELD(Products_Used, '|', $(INDX)) as tmp1_product_$(INDX),
SUBFIELD(Products_Used, '|', 200) as tmp1_product_200
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!