Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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!
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(Occupancy
Count)+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 !