Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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!