topic Re: Quick help around a simple loop in qlik sense in App Development

Quick help around a simple loop in qlik sense

nam — Sat, 16 Nov 2024 06:44:11 GMT

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

Re: Quick help around a simple loop in qlik sense

nam — Mon, 28 Jan 2019 19:26:46 GMT

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

Re: Quick help around a simple loop in qlik sense

rwunderlich — Mon, 28 Jan 2019 19:34:09 GMT

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

Re: Quick help around a simple loop in qlik sense

nam — Mon, 28 Jan 2019 19:36:54 GMT

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