How to take multiple values from one field into many rows
Hello,
I have a table with tracking numbers similar to the following. If an order num has multiple tracking numbers, the tracking numbers are separated by <BR> in the data, but I need each tracking number to have it's own row
Current Table:
Order Num, Tracking Num 1234, 556644 2345, 112233<BR>445566
Desired Table:
Order Num, Tracking Num 1234, 556644 2345, 112233 2345, 445566
It sounds like there's an indeterminate amount of <BR> values in the column so I have written a loop based on the maximum number of sections in the column. It sounds like the original data was in an HTML format:
data: load [Order Num], [Tracking Num], // Find the rows containing breaks SubStringCount([Tracking Num],'<BR>')+1 as BreakCount ; load * Inline [ Order Num, Tracking Num 1234, 556644 1235, 532345 2345, 112233<BR>445566 3245, 135905 5123, 335555<BR>909090<BR>539234 3362, 333111<BR>912356 ];
//****************************** // Find the maximum number of breaks in the columns // To be used in the loop MaxBreak: load max(counter) as MaxBreaks; Load fieldvalue('BreakCount', recno()) as counter AutoGenerate fieldvaluecount('BreakCount');
let vMaxBreaks = peek('MaxBreaks',0,'MaxBreak'); //******************************
// Loop through your data from 1 to the number of sections in the column // splitting them up using subfield for i = 1 to vMaxBreaks
Tracking: Load [Order Num], SubField([Tracking Num],'<BR>',$(i)) as [Tracking Num] Resident data where not isnull(SubField([Tracking Num],'<BR>',$(i)));
next i;
// Drop unrequired tables drop table data, MaxBreak;
// clean up the variables let vMaxBreaks=; let i=;
