Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #36: Data Cleansing Extract Authors

Highlighted
Alteryx Alumni (Retired)

The link to the solution for last challenge #35 is HERE

 

Use Case:  An analytical consulting company downloads medical journal publication data from the web and would like to extract all of the authors for the listed entries.

 

The text input contains details about each article where FAU indicates the author name for the article – in most case there are multiple authors. The details of each article are contained in lines that begin with PMID and end with an empty line.

 

Objective: Parse out each article PMID and list each author in sequential columns as seen in the Results.yxmd file.

Highlighted
Alteryx
Alteryx

A solution has been included 

Spoiler
2016-08-15 08_47_29-Alteryx Designer x64 BETA - DataPrep_ExtractAuthors_Intermediate_Solution.yxmd_.png
Tara McCoy
Highlighted
Alteryx Certified Partner

 

Spoiler

ALteryx weekly exercise 36.PNG

My approach

 

Highlighted
Alteryx Alumni (Retired)

Very nice use of the 'select records' tool @Naledi

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@JoeM,

 

Another attempt to earn my challenge badge(s).

 

Cheers,

Mark

 

P.S.  I'm glad to see @TaraM is so active in the challenges :)

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
16 - Nebula
16 - Nebula

Slightly different approach

 

Spoiler
split the data into 2 streams - article headers; and authors
Then processed the authors into a cross-tab in 2 ways (for practice)
- first was a simple ArticleAuthorID (using a multi-row formula) and then crosstab
- Second was to use a summarize to concatenate into one delimited field, then use TextToColumns to do the same as a crosstab


Highlighted
14 - Magnetar
14 - Magnetar

Solved!

 

Spoiler
WeeklyChallenge36.JPG
Highlighted
14 - Magnetar
14 - Magnetar

I used the Summarize tool to concatenate all of the names per PMID, and then parsed back into columns with Text to Cols

Spoiler
Spoilerimage.png
Highlighted
Alteryx Certified Partner

This was fun, it would have been much easier if you could group when creating a RecordId.....

Spoiler
But as an easy alternative I gave every record a count of 1 and then used running total :)

Weekly Challenge 36.png
Highlighted
Alteryx Certified Partner

Solution attached