How to take multiple values from one field into many rows

[CaseyLassiter]

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

Any thoughts?

[anthonyj]

Hi @CaseyLassiter,

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

trace
****************
$(i)
$(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=;

I hope this helps with your requirement.

Thanks

Anthony

[tresesco · Accepted solution]

Look at the subfield() for easier solution.

[anat]

load  [Order Num],subfield([ Tracking Num],'<BR>') as [ Tracking Num];

load * inline [

Order Num, Tracking Num
1234, 556644
2345, 112233<BR>445566

];

[anthonyj]

That's even easier. Didn't know the 3rd parameter was optional. Qlik never ceases to amaze me!

Thanks

[anat]

Yes, 3rd parameter optional. 

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