How to iterate a field name in a loop(Solved)

[manuelsavini]

Hi all, I'm new to Qlik and to this forum so i hope to be as more clear as i can

I have a table made like this :

%Ticket, Ticket1,DateStart1, DateFinish1,  Ticket2,DateStart2, DateFinish2,  Ticket3,DateStart3, DateFinish3

0,               abc1,       0-0-0000,    0-0-0000,         abc2,    0-0-0000,      0-0-0000,        abc3,     0-0-0000,     0-0-0000        

1,               abc4,       0-0-0000,    0-0-0000,         abc5,    0-0-0000,      0-0-0000,        abc6,     0-0-0000,     0-0-0000     

and the result that i want it's a table like this:

%Ticket, descTicket, DateStart,DateFinish

0,               abc1,            0-0-0000,    0-0-0000

0,              abc2,             0-0-0000,    0-0-0000

0,              abc3,             0-0-0000,   0-0-0000

1,               abc4,            0-0-0000,    0-0-0000

1,              abc5,             0-0-0000,    0-0-0000

1,              abc6,             0-0-0000,   0-0-0000

The number of tickets continues for hundreds of columns, so i would like to do this in a loop.

Thanks

[dplr-rn]

try something like below

basically use variables inside the loop to  get the correct column name.

i am assuming we wont know for sure how many Ticket#  will be there so we need to make sure the loop terminates gracefully so use errormode to handle the same. 

Testloop:
load * inline
[
%Ticket, Ticket1,DateStart1, DateFinish1,  Ticket2,DateStart2, DateFinish2,  Ticket3,DateStart3, DateFinish3

0,               abc1,       0-0-0000,    0-0-0000,         abc2,    0-0-0000,      0-0-0000,        abc3,     0-0-0000,     0-0-0000        

1,               abc4,       0-0-0000,    0-0-0000,         abc5,    0-0-0000,      0-0-0000,        abc6,     0-0-0000,     0-0-0000 
];

set ErrorMode =0; // to ensure a field not found error in loop is handled properly
for i=1 to 1000 //set to a random high number 1000
	load 
	%Ticket
//use variable i to get the correct column
	, Ticket$(i) as descTicket
	,DateStart$(i) as DateStart
	,DateFinish$(i) as DateFinish
	Resident Testloop;
			
	if (ScriptError=11) then

		trace 'Script Error  Field not found';

		exit for;
	end if
next;
set ErrorMode =1; // set it back to normal
drop table Testloop;	

[petter]

SourceTable:
LOAD * FROM SourceTable.csv (txt);

nColumns = (NoOfColumns('SourceTable')-1)/3;
FOR column=1 TO nColumns
  DEST_TABLE:
  LOAD
   %Ticket,
   Ticket$(column) AS descTicket,
   DateStart$(column) AS DateStart,
   DateFinish$(column) AS DateFinish
  RESIDENT
   SourceTable;
NEXT

DROP TABLE SourceTable;

There is a more efficient solution to this that doesn't need to use loops and runs much faster

[JustinDallas · Accepted solution]

You can dynamically create the column names that you need, and then use that with a FOR...Loop to tie it all together.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

Here is your demo data in action.

StartTable:
LOAD * Inline
[
%Ticket, Ticket1,DateStart1,DateFinish1,Ticket2,DateStart2,DateFinish2,Ticket3,DateStart3,DateFinish3
0, abc1,0-0-0000,0-0-0000,abc2,0-0-0000,0-0-0000,abc3,0-0-0000,0-0-0000        
1,abc4, 0-0-0000,0-0-0000,abc5,0-0-0000,0-0-0000,abc6,0-0-0000,0-0-0000    
]
;

NewTable:
LOAD * Inline
[
	'DummyColumn'
]
;

FOR i = 1 to 3
  LET vTicketField = 'Ticket'&$(i)
  ;
  LET vDateStartField = 'DateStart'&$(i)
  ;
  LET vDateFinishField = 'DateFinish'&$(i)
  ;

  Concatenate(NewTable)
  LOAD %Ticket,
  		$(vTicketField) AS 'descTicket', 
  		$(vDateStartField) AS 'DateStart', 
        $(vDateFinishField)  AS 'DateFinish' 
        RESIDENT StartTable
  ;
NEXT i
;

DROP TABLE StartTable
;
DROP FIELD DummyColumn
;
EXIT Script
;

I assume that every Ticket/DateStart/DateFinish tuple is complete and that the numbers are contiguous in existences i.e Ticket4/DateStart4/DateFinish4 doesn't get skipped in the data.

[manuelsavini]

Thank you all for answering, i fixed my script and learnt more about the methodologies you used