community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Need help with preperation of data

Asteroid

Hi ,

 

Having data like this , how to make the data as below screen shot.

 

Input 

 

LocationIDDeptMembers
SP1ScienceA1 - 34543 A1 - 56789
MA2MathsB1 - 876649 A1 - 8423423 C1 - 324234
MT3ArtsD1 - 543542 F1 - 254524
GO4EngineeringS1 - 53254
GA5SocialP1 - 412412 D - 4134141 

 

Result

LocationIDDeptMembers
SP1ScienceA1 - 34543
SP1ScienceA1 - 56789
MA2MathsB1 - 876649
MA2MathsA1 - 8423423
MA2MathsC1 - 324234
MT3ArtsD1 - 543542
MT3ArtsF1 - 254524
GO4EngineeringS1 - 53254
GA5SocialP1 - 412412
GA5SocialD - 4134141

 

 

Please let me know to transform data like that.

 

Thanks

 

Highlighted
Alteryx Certified Partner

@shankar12,

 

use the below expression in the regex tool on members field

 

\w\d\s\-\s\w+

 

Output method : Tokenize  then split to rows.

 

Thanks,

Vishwa

Asteroid
LocationIDDeptMembers
SP1ScienceA1 34543 A1 -56789
MA2MathsB1 - 876649 A1 - 8423423 C1 - 324234
MT3ArtsD1 - 543542 - F1-254524
GO4EngineeringS1 - 53254
GA5SocialP1 -412412 D- 4134141 
    
Result   
LocationIDDeptMembers
SP1ScienceA1 - 34543
SP1ScienceA1 - 56789
MA2MathsB1 - 876649
MA2MathsA1 - 8423423
MA2MathsC1 - 324234
MT3ArtsD1 - 543542
MT3ArtsF1 - 254524
GO4EngineeringS1 - 53254
GA5SocialP1 - 412412
GA5SocialD - 4134141

 

Thanks viswa your solution helped me partially, i still have some problem the "-" and the spaces are  irregular.is there any solution for thsi.

 

Thanks.

Alteryx Certified Partner

Use this expression:

 

\w*\s*\-\s*\w+

 

Thanks,

Vishwa

Alteryx Certified Partner
Alteryx Certified Partner
When I look at the data I see a space followed by a letter as the key. \s\u wants to be replaced with a | symbol.

Then it would be easy to split to rows.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

Got it.Thanks Vishwa. 

Labels