Quick help around a simple loop in qlik sense

[nam]

Hi can anyone help with a quick script to convert original table into New table, essentially create a new row for each value in the reduction field .

Original table
ACCESS	USERID	REDUCTIONFIELD
USER	DOMAIN\USERID1	VALUE1/VALUE2/VALUE3
USER	DOMAIN\USERID2	VALUE1/VALUE2
New Table
ACCESS	USERID	REDUCTIONFIELD
USER	DOMAIN\USERID1	VALUE1
USER	DOMAIN\USERID1	VALUE2
USER	DOMAIN\USERID1	VALUE3
USER	DOMAIN\USERID2	VALUE1
USER	DOMAIN\USERID2	VALUE2

[nam · Accepted solution]

here is my script so far:
table1:
LOAD
"ACCESS",
"USERID",
REDUCTIONFIELD,
SubStringCount(REDUCTIONFIELD, '/') as NoOfValues
FROM [lib://loop/loop.xlsx]
(ooxml, embedded labels, table is Sheet2);



FOR RecordNumber = 1 TO (NoOfRows('table1'))
Let NoOfValues = Peek('NoOfValues',0, 'Temp_table1');
For vI=0 to $(vNoOfValues)+1
table2:
LOAD
"ACCESS",
"USERID",
REDUCTIONFIELD,
SubField(REDUCTIONFIELD, '/', $(vI)) as ReductionField
Resident table1;
Next
Next
drop table table1;

and my current output is not correct

[rwunderlich · Accepted solution]

You are working too hard. 🙂 Use the SubField() function to generate a new row for REDUCTIONFIELD value. 

table2:
LOAD
"ACCESS",
"USERID",
SubField(REDUCTIONFIELD, '/')
FROM [lib://loop/loop.xlsx]
(ooxml, embedded labels, table is Sheet2);

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[nam]

Yes of course .... thank you. i  was going off tangent