separate each value which are concatenated in a single cell by batch.
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
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.
- Labels:
- Preparation
- Transformation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @stj1120 ,
acDuration column datatype is double.
numberOfStops - you can use Text to column tool .
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Please help with the solution on how to get this resolved.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @atcodedog05
The value present in each cell present in this column. Please help to show the actual value in each cell.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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?
