Extract String into New Rows while maintaining Key?

[AD_Accel]

Hi all,

I have a concept in mind, but am struggling to express it within the Load Editor.

I have a table that looks like the following:

Key	All Occupancies	OccupancyCount
T: Estate Agency	Estate Agency	1
T: Residential / Tattooing	Residential / Tattooing	2
T: Post Offices / Pharmacy / Mechanics / Mechanics	Post Offices / Pharmacy / Mechanics / Mechanics	4
T: Lighting Retailing / Chiropractic / Office	Lighting Retailing / Chiropractic / Office	3

OccupancyCount returns the number of "phrases" between / outside of the forwards slash.

I want to be able to pull each Occupancy from AllOccupancies into its own row, whilst retaining the key, based on the number in Occupancy Count.

I think I need to nest a couple of For loops: one to loop through each row for the total number of OccupancyCount, and another one to perform the expansion based on the OccupancyCount within each row. It will then need to possibly use SubField (?) to extract each phrase based on the row OccupancyCount and place it into a new row (which is where I think IterNo comes in). I can't quite get my head round what this should look like in practice, however!

I'd like the resultant table to look like as follows:

Key	All Occupancies	OccupancyNum
T: Estate Agency	Estate Agency	1
T: Residential / Tattooing	Residential	1
T: Residential / Tattooing	Tattooing	2
T: Post Offices / Pharmacy / Mechanics / Mechanics	Post Offices	1
T: Post Offices / Pharmacy / Mechanics / Mechanics	Pharmacy	2
T: Post Offices / Pharmacy / Mechanics / Mechanics	Mechanics	3
T: Post Offices / Pharmacy / Mechanics / Mechanics	Mechanics	4
T: Lighting Retailing / Chiropractic / Office	Lighting Retailing	1
T: Lighting Retailing / Chiropractic / Office	Chiropractic	2
T: Lighting Retailing / Chiropractic / Office	Office	3

Hopefully someone can give me a few pointers.

Thanks in advance

[HugoRomeira_PT · Accepted solution]

Hello,

You can use the subfield function to split the "All occupancies" string into rows, and than create a row id grouping by key, to do this a simple if and peek() function can make it work.

Solution bellow (I've loaded your data using a inline table, but you should be able to easially replace it):

OccupanciesTable:
LOAD * Inline
[Key,All Occupancies,OccupancyCount
T: Estate Agency,Estate Agency,1
T: Residential / Tattooing,Residential / Tattooing,2
T: Post Offices / Pharmacy / Mechanics / Mechanics,Post Offices / Pharmacy / Mechanics / Mechanics,4
T: Lighting Retailing / Chiropractic / Office,Lighting Retailing / Chiropractic / Office,3]
;

NoConcatenate
T_OccupanciesTable:
LOAD Key,
SubField(trim([All Occupancies]),'/') as [All Occupancies], /*SubField() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter, in your case "/".*/
if(Peek(Key)=Key,peek(OccupancyCount)+1,1) as OccupancyNum /*Simulate a row ID, to count the position of each string grouped by Key, it's very important to keep the order by Key*/

Resident OccupanciesTable
Order by Key;

DROP Table OccupanciesTable;

Exit script;

Hope it helps!

[AD_Accel]

Amazing: thank you! I can't believe how simple that was!

There were only adjustments I needed to make to your script:

---

if(Peek(Key)=Key,peek(OccupancyCount)+1,1) as OccupancyNum /*Simulate a row ID, to count the position of each string grouped by Key, it's very important to keep the order by Key*/

---

I rewrote as:

if(Peek(Key)=Key,peek(OccupancyNum)+1,1) as OccupancyNum

---

And:

SubField(trim([All Occupancies]),'/') as [All Occupancies], /*SubField() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter, in your case "/".*/

---

With the following one, I needed to keep all spaces due to how some of the strings are written, and have a space either side of the forward slash due to some other strings having them as part of the "phrase":

SubField([All Occupancies],' / ') as [All Occupancies],

---

Thanks so much @HugoRomeira_PT !