Challenge #36: Data Cleansing Extract Authors

[GeneR]

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.

[TaraM]

A solution has been included 

Spoiler

[Naledi]

Spoiler

My approach

[GeneR]

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

[MarqueeCrew]

@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 :)

[SeanAdams]

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

[NicoleJohnson]

Solved!

Spoiler

[estherb47]

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

Spoiler

Spoiler

[LordNeilLord]

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 :)

[nick_ceneviva]

Solution attached