Parse string with one or multiple delimiters using RegEx

[LEXQ2005]

Table 1 Input is the initial data:

Order ID	Order Qty	Status ID	Status	Scenario
OR_000001	1	473717;	Delivered;	Order Qty = # of Status (Normal)
OR_000002	10	494086;94089;494090;494099;494100;494101;494102;494103;494104;494105	Delivery Started;;Delivered;Delivered;Delivery Started;Delivered;Delivery Started;Delivery Started;Delivery Started;Delivered;	one order with blank status
OR_000003	7	496038;517460;530925;	Delivered;Delivered;Cancelled;	Order Qty > # of Status

Table 2 Output is the desired Alteryx output:

Order ID	Order Qty	StatusID	Status
OR_000001	1	473717	Delivered
OR_000002	10	494086	Delivery Started
OR_000002	10	94089
OR_000002	10	494090	Delivered
OR_000002	10	494099	Delivered
OR_000002	10	494100	Delivery Started
OR_000002	10	494101	Delivered
OR_000002	10	494102	Delivery Started
OR_000002	10	494103	Delivery Started
OR_000002	10	494104	Delivery Started
OR_000002	10	494105	Delivered
OR_000003	7	496038	Delivered
OR_000003	7	517460	Delivered
OR_000003	7	530925	Cancelled

How can I use RegEx to parse the StatusID and Status in table 1 by semicolon to generate table 2?

The sample data file is attached. Thanks. 

[atcodedog05 · Accepted solution]

Hi @LEXQ2005 

Here is a workflow for the task.

Output:

Workflow:

Text to column split to rows on delimiter ;

Need to handle status ID and status separately

Tile tool give sub ids based on OrderID Order Qty

Join them together

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[LEXQ2005]

It's a great solution. Thanks! @ atcodedog05

I know RegEx is mighty, I'm still interested in hearing about a RegEx solution for this problem. 

[atcodedog05]

Hi @LEXQ2005 

Regex is really mighty when it comes to splitting text to multiple columns.

Splitting to row best possible alternative using regex tool would be tokenize split to rows. Which would be similar to this.

Happy to help 🙂

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[LEXQ2005]

Thanks! 

Is that possible to break down to rows by the number of Order Qty for each Order?

The data input is:

Order ID	Order Qty	Status ID	Status	Scenario
OR_000001	1	473717;	Delivered;	Order Qty = # of Status (Normal)
OR_000002	10	494086;94089;494090;494099;494100;494101;494102;494103;494104;494105	Delivery Started;;Delivered;Delivered;Delivery Started;Delivered;Delivery Started;Delivery Started;Delivery Started;Delivered;	one order with blank status
OR_000003	7	496038;517460;530925;	Delivered;Delivered;Cancelled;	Order Qty > # of Status
OR_000004	2			the whole order not yet has a status mark

The desired Alteryx output is:

Order ID	Total Order Qty	Order Qty	StatusID	Status
OR_000001	1	1	473717	Delivered
OR_000002	10	1	494086	Delivery Started
OR_000002	10	1	94089
OR_000002	10	1	494090	Delivered
OR_000002	10	1	494099	Delivered
OR_000002	10	1	494100	Delivery Started
OR_000002	10	1	494101	Delivered
OR_000002	10	1	494102	Delivery Started
OR_000002	10	1	494103	Delivery Started
OR_000002	10	1	494104	Delivery Started
OR_000002	10	1	494105	Delivered
OR_000003	7	1	496038	Delivered
OR_000003	7	1	517460	Delivered
OR_000003	7	1	530925	Cancelled
OR_000003	7	1
OR_000003	7	1
OR_000003	7	1
OR_000003	7	1
OR_000004	2	1
OR_000004	2	1

The sample data file is attached. 

[atcodedog05 · Accepted solution]

Hi @LEXQ2005 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍