I am struggling parsing a long string properly.
******As an FYI Alteryx thought my strings were HTML so I had to add " " around the examples and the field names, which aren't actually there in real life********
I need to pop out the following fields from dynamic string strings that can look like this;
Example 1:
"Proj for ONT Ntwk/Table Prog () from (Olivia Lenox) - D/V only - Various sites, Phones= 7290, Comp Phones= 0"
Example 2:
"Proj for DNT Ntwk/Table Prog (MR2923021) from (Jill Lady)- Expansion-Place Center, Floors Affected=1- Seats Effected=48, Circuit=Yes , ConfRm=3, WAPs=0, WIPs=0, Phones=0 ,Comp ConfRm= 0, Comp WAP's= 0, Comp WIP's= 0, Comp Phones= 0"
As you can see, literally nothing about the strings are guaranteed. They could have the fields I need to pull which are;
"ConfRm, WAPs, WIPs, Phones, Comp ConfRm, Comp WAP's, Comp WIP's, Comp Phones"
I know I would probably have to do each parsing separately and Im ok with that. The fields don't previously exist in my dataset, so I can create them in a formula tool beforehand or during this process. To clarify, I really only need the value that is after the = sign. I dont need the space but it also wouldnt matter if the space was pulled in. Attached are the two different ways I tried to tackle this but I was not successful as I cannot figure out how to parse dynamically for strings like this.
Hi, @livluvlaf4lyf
If you can give the desired input result data, we will show you a more precise process.
Tokenize by :
(?<=,)([^,-]+?)(?=,|$)
Input |
Proj for DNT Ntwk/Table Prog (MR2923021) from (Jill Lady)- Expansion-Place Center, Floors Affected=1- Seats Effected=48, Circuit=Yes , ConfRm=3, WAPs=0, WIPs=0, Phones=0 ,Comp ConfRm= 0, Comp WAP's= 0, Comp WIP's= 0, Comp Phones= 0 |
Proj for ONT Ntwk/Table Prog () from (Olivia Lenox) - D/V only - Various sites, Phones= 7290, Comp Phones= 0 |
Output |
Circuit,ConfRm,WAPs,WIPs,Phones,Comp ConfRm,Comp WAP's,Comp WIP's,Comp Phones |
Phones,Comp Phones |
Thank you so much! My desired output is to pop out or overwrite these field headers “ConfRm, WAPs,WIPs, Phones,Comp ConfRm, Comp WAP's, Comp WIP's, Comp Phones” and then populate every record in my dataset with whatever value(s) are after the equal sign. However, if one of these values dont exist, i cant have “0” populate it. For example, the ONT network example doesnt have ConfRm. I still need ConfRm to show up as a header field for that record… but I cant have it automatically populate 0. It will need to populate either N/A or a blank space. As unfortunately I need the 0 and 1 values to only be there when they actually exist for calculations later on.
Could you also explain the language of the token by chance?
Hi, @livluvlaf4lyf
1- Please upload sample output table as your want. (format of above post.)
2- Regex syntax:
(?<=,) after ,
([^,-]+?) exclude , and -
(?=,|$) before , or end.
Hopefully this does the trick! I am fine either creating the fields during this process or creating them before I get to this point in the workflow.
Input | |||||||||||
Description | |||||||||||
Proj for DNT Ntwk/Table Prog (MR2923021) from (Jill Lady)- Expansion-Place Center, Floors Affected=1- Seats Effected=48, Circuit=Yes , ConfRm=3, WAPs=0, WIPs=0, Phones=0 ,Comp ConfRm= 0, Comp WAP's= 0, Comp WIP's= 0, Comp Phones= 0 | |||||||||||
Proj for ONT Ntwk/Table Prog () from (Olivia Lenox) - D/V only - Various sites, Phones= 7290, Comp Phones= 0 | |||||||||||
Output | Description | ConfRm | WAPs | Wips | Phones | Comp Confm | Comp WAP's | Comp Wip's | Comp Phones | ||
Proj for DNT Ntwk/Table Prog (MR2923021) from (Jill Lady)- Expansion-Place Center, Floors Affected=1- Seats Effected=48, Circuit=Yes , ConfRm=3, WAPs=0, WIPs=0, Phones=0 ,Comp ConfRm= 0, Comp WAP's= 0, Comp WIP's= 0, Comp Phones= 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
Proj for ONT Ntwk/Table Prog () from (Olivia Lenox) - D/V only - Various sites, Phones= 7290, Comp Phones= 0 | N/A | N/A | N/A | 7290 | N/A | N/A | N/A | 0 | |||
OR the output below for description example #2 | I am fine with the null fields being N/A or " " , but they cannot automatically populate with a zero. | ||||||||||
Proj for ONT Ntwk/Table Prog () from (Olivia Lenox) - D/V only - Various sites, Phones= 7290, Comp Phones= 0 | 7290 | 0 |
Hi, @livluvlaf4lyf
FYI.
Input | ||||||||
Description | ||||||||
Proj for DNT Ntwk/Table Prog (MR2923021) from (Jill Lady)- Expansion-Place Center, Floors Affected=1- Seats Effected=48, Circuit=Yes , ConfRm=3, WAPs=0, WIPs=0, Phones=0 ,Comp ConfRm= 0, Comp WAP's= 0, Comp WIP's= 0, Comp Phones= 0 | ||||||||
Proj for ONT Ntwk/Table Prog () from (Olivia Lenox) - D/V only - Various sites, Phones= 7290, Comp Phones= 0 | ||||||||
Output | ||||||||
RecordID | ConfRm | WAPs | WIPs | Phones | Comp ConfRm | Comp WAP's | Comp WIP's | Comp Phones |
1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | 7290 | 0 |
Could you link the example workflow like some people do on this community? Im having trouble getting "Find&Replace", "crosstab", and "dynamic rename" to work. In your screenshot, I can only see the workflow itself and not what you are putting into the actual tool in the left side drop down.
When I ran your solution (I didnt edit it at all), it gave me this error, (RegEx (11) line)