How to Expand Multiple ArrayS to rows ?

[Marc-LEVIN]

Hi 😀

I want to do this :

FROM :

col1 row1 : ["item1.1", "item1.2", "item1.3"] | col2 row1 : ["item2.1", "item2.2", "item2.3"]

TO :

col1 row1 : item1.1 | col2 row1 : item2.1

col1 row2 : item1.2 | col2 row2 : item2.2

col1 row3 : item1.3 | col2 row3 : item2.3

It's ok for a single Array (fuction Expand Array To Rows 👍 ) but not ok for multiple Arrays 😓

Can you help me ?

Sorry for my English => i'm French 😉

Thanks for your help !

Regards.

[AMiller_Tri · Accepted solution]

Hi @Marc LEVIN? ,

sorry but I'm a bit confused.

scenario A

You have multiple arrays, nested within another array, in a single field.

In that case - you can use the arraymergeelements() function, in order to condense the inner-arrays to separate arrays.

Scenario B

You have 2 columns, an array in each. Each array's length is 3. You'd want for Record X to have items 1.X, 2.X

If this is the case - you should use arrayzip() on these 2 columns - so you'd have ["item1.1,item2.1","item1.2,item2.2","item1.3,item2.3"]. Then you can Flatten this array.

so you'll have 3 records (and only 1 column - we'll fix that in a moment)

col1

item1.1,item2.1

item1.2,item2.2

item1.3,item2.3

Now, just split by delimiter, having that delimiter comma. Then, you'd have 2 columns - with the values you want.

Hope one of these scenarios is what you're looking for.

Thanks,

Amit.

[Marc-LEVIN]

Hi @Amit Miller? 

Thanks for your answer.

That's the Scenario B !

It's OK ! The fonction arrayzip() is the good solution !

I did'nt know this function ! Amazing !

It's perfect !

Thanks again. 😁

[AMiller_Tri]

Sure Marc, glad I could be of help.

In general, when you're lost with arrays, you can search for "array" in the "search transformation" field, and read about the different functions involving arrays - the solution might be hiding there =]

[Marc-LEVIN]

👍

[Trifacta_Alumni]

Documentation on array and other nested functions: http://docs.trifacta.com/display/DP/Nested+Functions

Above links are for Cloud Dataprep.