LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: what_to_do

Hi

I�m try to split a string into multiple columns. Due to the variable length of the data i am unable touse/call each component of the array (using �split�) directly.

Could you please advise of an easier way of doing this. Below is the sample input and desired output.


Input:

Items
Tea 1, Spoons 4, ball e32, Magazine 425
Paper 21, desk 88, cup 3


Output:
Item1 Item2 Item3 Item4 Item5 Item6
Tea Spoons ball Magazine
Paper desk cup

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Sure, I attached an example. You need to use Expand From List, which will split the items, but do it by adding rows of data. Before doing that, I added a rowId so I could later make sure to assemble back to the original rows.

Then I split the data by a space, and took the first argument, as it looked like each item had a two values, seperated by a space, and you only wanted the first one.

I used an agg to label each of the items, resetting my count when I got a new rowId. After that, it is a simple pivot to rotate the data back again.

The pivot is a new 4.5 node, but you could use a rotate node in it's place...however with rotate, you need to have the first set of data be the longest...see the two additional examples, where the first one fails because the longest row isn't the first. The second simply prepends a row with a dummy longest set of data and then strip it out at the end.

Cheers
Rich
Attachments:
Example.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: what_to_do

Hi Rich,

Thank you for the quick response and solution.

There seems to be a limitation with the Rotate node if the "Items" in the second set of data is greater (See attached sample file)?
Attachments:
Rotate Limit.txt

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Yes, that's correct, sorry if I wasn't clear; I attempted to explain that in the last paragraph.

			
				you could use a rotate node in it's place...however with rotate, you need to have the first set of data be the longest...see the two additional examples, where the first one fails because the longest row isn't the first. The second simply prepends a row with a dummy longest set of data and then strip it out at the end.
			

In the second example in the graph, I prepend a record with the longest expected value, and then later, I drop that first record. So if you have an idea of how long your longest could be, you can just make sure you have a dummy row up front that is that long or longer.

You could also write a couple of nodes that takes a pass at your data counting the biggest set, and then a node that creates that dummy line for you based on your data, so it is dynmanic. If you need help with that logic, let me know.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: what_to_do

Hi Rich,

Thank you for the reply....Dummy data is the way to go.

Regards

Pete