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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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

 

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.
Highlighted
Asteroid

Got it.Thanks Vishwa. 

Labels