Unpivoting
- 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, I have a dataset in the following structure, reflecting IDs of Parent Legal entities in one column, with the IDs of their Subsidiary Legal entities in a second (Subs). Some Parents have zero Subs.
Parent | Subs |
654 | - |
843 | 186; 175; 325; 211 |
184 | - |
746 | 514; 851; 222 |
567 | 117 |
962 | - |
I want to convert that into a set where the first column indicates all the IDs )Parents + Subs) and then the second column what the parent entity is, or, if it is a parent entity itself "Parent". Like this:
ID | Parent ID |
654 | Parent |
843 | Parent |
186 | 843 |
175 | 843 |
325 | 843 |
211 | 843 |
184 | Parent |
746 | Parent |
514 | 746 |
851 | 746 |
222 | 746 |
567 | Parent |
117 | 567 |
962 | Parent |
It requires splitting the Subs column by delimiter, then unpivot, but also adding a record for the Parent itself. I could not figure out the last step...anybody who can help?
- Labels:
- Transformation
- 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 @SebastianRo,
I made a slight modification, but similar approach to the others. I start by splitting to rows on the semicolon using Text to Columns, then replace the dashes with the Parent ID and union to ensure all IDs, including Parents with Subsidiaries are listed. Attached is a workflow for reference.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @SebastianRo how's this:
First you flag the IDs with no subs as Parent, then flip out all IDs, then you can join back on to get their parent ID and flag the fall out as Parents too.
Hope that helps,
Ollie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @binuacs I'm not sure that's right. The ID column (parent in yours) should be unique and show all 14 values, whereas your values are split over both columns
- 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
Thanks for this. The step missing here is the additional record for "Parent" for Parent IDs that have Subs. The 843 for example:
843 | Parent |
186 | 843 |
175 | 843 |
325 | 843 |
211 | 843 |
- 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