topic Re: How to iterate a field name in a loop in QlikView

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

manuelsavini — Wed, 29 Jan 2020 20:31:32 GMT

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

Re: How to iterate a field name in a loop

dplr-rn — Wed, 29 Jan 2020 19:11:17 GMT

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;

Re: How to iterate a field name in a loop

petter — Wed, 29 Jan 2020 19:12:41 GMT

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

Re: How to iterate a field name in a loop

JustinDallas — Wed, 29 Jan 2020 19:25:07 GMT

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/ScriptControlStatements/For.htm

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.

Re: How to iterate a field name in a loop

manuelsavini — Wed, 29 Jan 2020 20:15:33 GMT

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