Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can dynamically create the column names that you need, and then use that with a FOR...Loop to tie it all together.
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.
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;
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
You can dynamically create the column names that you need, and then use that with a FOR...Loop to tie it all together.
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.
Thank you all for answering, i fixed my script and learnt more about the methodologies you used