Parsing multiple fields

[kyle_mccallan]

Hi,  

I need to parse multiple fields and was in search of a way to do this in Alteryx without having to drop in a large number of text to columns.  

The data 

1	2	. . .
[Product],[Date]	[Product],[Date]	. . .
[Product],[Date]	[Product],[Date]	. . .
		. . .

to

1	1	2	2
[Product]	[Date]	[Product]	[Date]
[Product]	[Date]	[Product]	[Date]

I need to be able to do this on 200+ fields, does anyone know if alteryx can handle that?'

-Kyle 

[MarqueeCrew]

@kyle_mccallan,

What format is your data being received in?  If it comes to you in a format like CSV, I would read it in without any delimiter (use \0 as a deliimiter) and then I would parse the data via regular expressions.  If that isn't feasible, I might then TRANSPOSE the data and then parse the VALUE field.  Once I did that, I would CROSSTAB the data back together.  This would all make more sense if you could provide some sample input data for us to demonstrate with.

Cheers,

Mark

[kyle_mccallan]

The data is retrieved from SQL originally and goes through some alteryx code so that I can join multiple databases and filter out unnecessary data.  The result is a yxdb file like dummy data for forum 1.  The same customer number can come up in multiple rows and the same product and date can even occur (customers buying two of the same product at the same time).  

I first use a formula to concatenate the product and date information.

Code:

[Date]+','+[Product]

Then I use a multi-row formula to create a unique ID  per customer product.  

Code:

(IF [CustomerID]=[Row-1:CustomerID] THEN [Row-1: Product#]+1 ELSE 1 ENDIF)

(*note: in the above Row-1: Product there is no space between the colon and Product, a smiley face came up and I am not sure how to stop that so I put the space there for now)

A colleague of mine found the following article and thinks that batch macros could be a solution.

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Configuring-Batch-Macro-Output-Fields-Horizon...

[kyle_mccallan]

*Sorry I hit post too early this is what the data to be parsed looks like.  So a cross tab is run so there is now a column for each data/product and now these need to be parsed into their own columns so product 1 date 1 product 2 date 2 and so on.  apologies for hitting post too early on my last.

[SophiaF · Accepted solution]

Hi @kyle_mccallan,

Here's the first thing that came to mind:

Essentially each multi-row formula parses out the first and second columns, respectively. Then, sort and deselect to arrange the rows properly. The expression is a simple substring:

Parsed column 1 (left of the pipe):

substring([_CurrentField_],0,1)

Parsed column 2 (right of the pipe):

substring([_CurrentField_],2)

If you set the tool to Copy Output Fields and Add a suffix, in the sort you can then sort alphabetically and 1_left and 1_right will  be next to each other. If you have a ton of fields, I would suggest looking into the Dynamic Select and Dynamic Sort tools to do some of the heavy lifting.

A couple of notes: this works if the "Product" has a standard number of characters, since it is based on the substring() function. I do think a batch macro would be a great option, but definitely a tad more advanced.

[kyle_mccallan]

Thanks Sophia,

Products do not have the same number of characters unfortunately, but I should be able to write a quick formula to transform them so they do, then after the substring expressions can undo the transformation after.