Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need help with preperation of data

shankar12
8 - 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

 

5 REPLIES 5
vishwa_0308
11 - Bolide

@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

shankar12
8 - 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.

vishwa_0308
11 - Bolide

Use this expression:

 

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

 

Thanks,

Vishwa

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
shankar12
8 - Asteroid

Got it.Thanks Vishwa. 

Labels