Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Employee
Employee

Quick help around a simple loop in qlik sense

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
2 Solutions

Accepted Solutions
Employee
Employee

Re: Quick help around a simple loop in qlik sense

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
MVP & Luminary
MVP & Luminary

Re: Quick help around a simple loop in qlik sense

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

3 Replies
Employee
Employee

Re: Quick help around a simple loop in qlik sense

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
MVP & Luminary
MVP & Luminary

Re: Quick help around a simple loop in qlik sense

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

Employee
Employee

Re: Quick help around a simple loop in qlik sense

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