Grouping data on multiple "headers"

[patrisk]

I am trying to get from this:

310 Shares

088 AUSTRALIA

Security Placeholder 1

Security Placeholder 2

Security Placeholder 3

Security Placeholder 4

Security Placeholder 5

Security Placeholder 6

202 CAYMAN ISLANDS

Security Placeholder 7

Security Placeholder 8

248 SOUTH KOREA

Security Placeholder 9

Security Placeholder 10

Security Placeholder 11

350 Mutual Fund Units

470 HONGKONG

Security Placeholder 12

Security Placeholder 13

534 JAPAN

Security Placeholder 14

To that:

310 Shares	088 AUSTRALIA	Security Placeholder 1
310 Shares	088 AUSTRALIA	Security Placeholder 2
310 Shares	088 AUSTRALIA	Security Placeholder 3
310 Shares	088 AUSTRALIA	Security Placeholder 4
310 Shares	088 AUSTRALIA	Security Placeholder 5
310 Shares	088 AUSTRALIA	Security Placeholder 6
310 Shares	202 CAYMAN ISLANDS	Security Placeholder 7
310 Shares	202 CAYMAN ISLANDS	Security Placeholder 8
310 Shares	248 SOUTH KOREA	Security Placeholder 9
310 Shares	248 SOUTH KOREA	Security Placeholder 10
310 Shares	248 SOUTH KOREA	Security Placeholder 11
350 Mutual Fund Units	470 HONGKONG	Security Placeholder 12
350 Mutual Fund Units	470 HONGKONG	Security Placeholder 13
350 Mutual Fund Units	534 JAPAN	Security Placeholder 14

This is only an excerpt from full data set. There are a lot more types of securities (first column) and a lot more countries (second column).

[atcodedog05 · Accepted solution]

Hi @patrisk 

Here is how you can do it.

Step-1: Separate the Labels into Groups

Step-2: Fill down the label using multi-row formula tool

Step-3: Filtering rows where Group-3 is empty

Hope this helps : )

[atcodedog05]

Hi @patrisk 

Also here are some resources on the tools used

Multi-row formula: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Regex: 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

https://regexr.com/

Hopefully, you learnt something new today which will help you in the future : )

Hope this helps : )

[patrisk]

Thank you so much! Consider yourself hugged :) 

As for Group 1, unfortunately I do not have a complete list of security types (e.g. Shares or Mutual Fund) available which could appear in one of my reports. Hence, I would need to apply a similar approach as with Group 2, i.e. applying REGEX_Match.

Unfortunately, I struggle with the pattern of this formula.

Reusing the one you made for Group 2, I would have imagined something like this, as the First letter is presumably always uppercase, followed by the second letter being lowercase.

IIF(REGEX_Match([Field1], "\d+\s[\u{1}\l{1}\s]+",0),[Field1], Null())

[atcodedog05 · Accepted solution]

Hi @patrisk 

You were close. Regex formula would be 

REGEX_Match([Field1], "\d+\s\u\l[\u\l\s]+",0)

After \u\l uppercase followed by lowercase we still need to give [\u\l\s]+ to accommodate other letters. And \u{1}\l{1} is same as \u\l so part was correct.

Hope this helps : )

[atcodedog05]

Happy to help : ) @patrisk 

Cheers and have a nice day!