Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How to transform one character column with row levels delimited by white spaces ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 8, 2017 9:07 AM
(3067 views)

I have a table with a structure of location names like the first column in below table that I want to get into separate columns. The geographical/administrative levels are defined by the use of white space before the entry. It is probably from a printed version of a data set. Have tried various ways, e.g., using UPPERCASE to select higher levels, and put them into columns. But it doesn't work for the lowercases, and is only a half-way solution. Is there a smart way to transfer the entries at each row level to separate columns as shown in the table? I'd prefer an interactive explanation if possible. Thanks a lot for any help.

2 ACCEPTED SOLUTIONS

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You can use column formulas. They will either copy a new level from the left in the current row or a repeating value from above the previous row. You can use the amount of white space (indentation) to determine which column should use the value. You will have some missing values for the intial rows and below where the previous column resets

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I attached a data table to show the approach I described at work. It doesn't handle special cases yet.

Learn it once, use it forever!

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You can use column formulas. They will either copy a new level from the left in the current row or a repeating value from above the previous row. You can use the amount of white space (indentation) to determine which column should use the value. You will have some missing values for the intial rows and below where the previous column resets

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to transform one character column with row levels delimited by white spaces into level colum

So you are wanting it to look like this?

If so, I created the columns as formulas and used the Item function to pull the proper pieces out of the Location column.

Dan Obermiller

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to transform one character column with row levels delimited by white spaces into level colum

Feb 8, 2017 10:03 AM
(3050 views)
| Posted in reply to message from Dan_Obermiller 02/08/2017 12:31 PM

Thank you. I've tried it but wouldn't work for me for the lowest level. Probably I'd entered the wrong number of white spaces in the Item formula.

However, is there a way to fill the columns for each row? Some Row subscript?

However, is there a way to fill the columns for each row? Some Row subscript?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I attached a data table to show the approach I described at work. It doesn't handle special cases yet.

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to transform one character column with row levels delimited by white spaces into level colum

Thanks a lot. Very smart with the formula Length, and the Lag functions. Can see I need to read more about these functions, especially the Lag function. Cheers, Jens