topic Re: How to take multiple values from one field into many rows in Connectivity & Data Prep

How to take multiple values from one field into many rows

CaseyLassiter — Thu, 07 Apr 2022 20:12:31 GMT

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 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 445566

Desired Table:

Order Num, Tracking Num
1234, 556644
2345, 112233
2345, 445566

Any thoughts?

Re: How to take multiple values from one field into many rows

anthonyj — Fri, 08 Apr 2022 01:47:27 GMT

Hi @CaseyLassiter,

It sounds like there's an indeterminate amount of 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],' ')+1 as BreakCount
;
load * Inline
[
Order Num, Tracking Num
1234, 556644
1235, 532345
2345, 112233 445566
3245, 135905
5123, 335555 909090 539234
3362, 333111 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],' ',$(i)) as [Tracking Num]
Resident data
where not isnull(SubField([Tracking Num],' ',$(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

Re: How to take multiple values from one field into many rows

tresB — Fri, 08 Apr 2022 06:26:20 GMT

Look at the subfield() for easier solution.

Re: How to take multiple values from one field into many rows

anat — Fri, 08 Apr 2022 06:41:12 GMT

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

load * inline [

Order Num, Tracking Num
1234, 556644
2345, 112233 445566

];

Re: How to take multiple values from one field into many rows

anthonyj — Sat, 09 Apr 2022 10:39:32 GMT

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

Thanks

Re: How to take multiple values from one field into many rows

anat — Sun, 10 Apr 2022 08:49:27 GMT

Yes, 3rd parameter optional.

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