What to do in subfield to automatically create number of column based on input data ???

[prahlad_infy]

Hello Sirs ,

What to do in subfield funcation to automatically create number of column based on input data ?
I am not wanting to manually write F1,F2,F3 ... etc . What i wante is to create number of fields ( F1,... Fn) using script based on VALUES . If value in VALUES is A,B,C,D,E,F,G then script should itselfly create fields  from F1 to F6 without me writing 

SubField(VALUES,',',1) AS F1 to F6

INLINEFEED :

TABLE:
LOAD * ,
SubField(VALUES,',',1) AS F1,
SubField(VALUES,',',2) AS F2,
SubField(VALUES,',',3) AS F3,
SubField(VALUES,',',4) AS F4 ;

LOAD * INLINE [

LISTS, VALUES
List 1, "A,B"
List 2, "A,B,C"
List 3, A
List 4, "C,D"
List 5, "E,F"
List 6, "E,E,G,H"
List 7, "O,O"
List 8, D
];

Thank you all .

[sunny_talwar · Accepted solution]

Try this script

Table:
LOAD *,
	 AutoNumber(RowNo(), LISTS) as SNo;
LOAD LISTS,
	 Subfield(VALUES, ',') as VALUES;
LOAD * INLINE [
    LISTS, VALUES
    List 1, "A,B"
    List 2, "A,B,C"
    List 3, A
    List 4, "C,D"
    List 5, "E,F"
    List 6, "E,E,G,H"
    List 7, "O,O"
    List 8, D
];

FinalTable:
LOAD DISTINCT LISTS
Resident Table;

FOR i = 1 to FieldValueCount('SNo')

	LET vSNo = FieldValue('SNo', $(i));
	
	Left Join (FinalTable)
	LOAD DISTINCT LISTS,
		 VALUES as [F$(vSNo)]
	Resident Table
	Where SNo = '$(vSNo)';
	
NEXT

DROP Table Table;

[prahlad_infy]

Thank You Sir for helping . Can this can be done by ITER ?
I am sorry if i am aking wrong question

[sunny_talwar]

What do you mean? Not sure I understand the question

[prahlad_infy]

I asking to know , if similar results can be achieved without using For loop ? Or using Iter No ..
Just asking , as i am new to looping thing in qlikview 

[sunny_talwar]

You can use Generic Load, but that will create multiple tables for you... and to combine them back you will need a for loop. So, to answer in short, I don't think you can do this without using a for loop.

[prahlad_infy]

OK. Can there be a solution using Iter No ? In Qlikview help it showing as a kind of looping function too

[sunny_talwar]

I don't think it is possible, but if you find a way, please share it with us here as I would love to learn another way of doing it.

[prahlad_infy]

Sorry Sir , i did not mean that there can be solution using iternumber function , i was asking if there is possible .
Sir ,You are genius here and when you saying not possible with ITERNO , how can i solve it ?

[sunny_talwar]

I am not offended by your question my friend, in fact it makes me curious if there is indeed a way to do it. I just don't have the time to find a solution using IterNo, but if you can or if someone can, I would love to know about it.

And please don't call me sir, my name is Sunny 🙂