Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
Announcement | We'll be doing maintenance between 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

separate each value which are concatenated in a single cell by batch.

stj1120
8 - Asteroid

Hi all,

 

I have a dataset where the values for each batch in the two columns are concatenated and separated by comma (,)  and shown in a single cell. 

 

I now want to separate each value in each cell in the acDuration and numberOfStops columns and show them one by one by each batch in each cell.

 

sreenivasateja_0-1645005875809.png

 

 

I thought to give a try using transpose tooland also I tried by using Text to columns tool by choosing Split to rows option but, no luck because, there are multiple values where in one cell there are 3 and in another cell there are 10 and in another there are 8 etc. So, this split to rows values for these two columns based on the batch number should be dynamic.

 

How to do that?

 

Please help.

 

best regards,

teja.

9 REPLIES 9
saveeshkumar
9 - Comet

Hi @stj1120 ,

 

acDuration column datatype is double.
numberOfStops - you can use Text to column tool .

Thanks.

binuacs
17 - Castor

@stj1120 after splitting the field "accDuration" for the first cell (14,537,237,393,851,400,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) returns 52 records, and the field memberOfStops returns (1,2,3,1,2,2,2,6,5,3,2,1,1,1,1,2,1,2,1,1,1,3,1,3,3)  25 records, how are you matching each accDuration with memberOfStops? It would be great if you upload the expected result for better understanding.

stj1120
8 - Asteroid

Hi @binuacs and @saveeshkumar 

 

I need the result to be like below screenshot for each batch. All the concatenated values should be splitted and each value should be shown in a each cell. 

 

sreenivasateja_0-1645085903468.png

Please help with the solution on how to get this resolved.

 

Thank you.

stj1120
8 - Asteroid

Hi @atcodedog05 

 

could you also check my issue and help with the solution?  I have many columns likethis with the concatenated cells. 

 

Please tell me how to achieve the result as above for all the similar cells at once.  

 

Thank you.

 

Best regards,

Teja.

atcodedog05
22 - Nova
22 - Nova

Hi @stj1120 

 

Is this what you are looking for 

 

atcodedog05_0-1645086793013.png

 

Hope this helps : )

 

stj1120
8 - Asteroid

Hi @atcodedog05 

 

Yes. Thanks a lot for checking and giving a quick response.

 

But, in the first field how can I see the actual value in each cell instead of a bigger value?

 

Could you check that and help me with the solution?

 

Thank you.

 

Best regards,

Teja.

atcodedog05
22 - Nova
22 - Nova

Hi @stj1120 

 

Which cell and what's the expected output.

 

 

stj1120
8 - Asteroid

Hi @atcodedog05 

 

The value present in each cell present in this column. Please help to show the actual value in each cell.

 

sreenivasateja_0-1645090195235.png

 

binuacs
17 - Castor

@stj1120 my question in the first cell value has around 52 value for the field accDuration

12,603,382,847,226,900,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

 

for the field numberOfStops has 24 records in the same cell. 

1,2,3,1,2,2,2,6,5,3,2,1,1,1,1,2,1,2,1,1,1,3,1,3,3

 

how are you going to map in this case? you can map first 24 what about the rest of the cell values in accDuration or am I missing something here?

 

Labels