Challenge #36: Data Cleansing Extract Authors

[grazitti_sapna]

Hi ,Please find my solution for the given challenge.

[terry10]

This exercise is very familiar; I know I've done this it before, perhaps as part of a training.

Spoiler

[Sylvana]

My solution.

[cc000072]

Here is my solution

Spoiler

[NaiLo]

It took me a while to figure out why I wasn't getting the same number of records as the solution; The "official" solution uses a method that filters out articles that don't have any authors listed 🙂

[izamryan]

I seem to be doing these challenges in more steps than some of the other solutions ... still need to work on my optimising!

Spoiler

asdf

I used 2 RegEx tools to slice out the PMID's and then the FAU's.
(PMID).*(\d{8}).*
(FAU - )(.*)

I then used Multi-Row formula tools to "fill" the rows - you need to do this step otherwise you can't associate the PMID's to the FAU's.

Using the Unique tool I then extracted out which are the unique combos of PMID/FAU.
A little Filter tool to clean up the data.

I then build out the column header for the Cross Tab so it can pivot the data

[deviseetharaman]

Spoiler

[jarrod]

was thinking about it too hard. came up with a simpler solution

Spoiler

[ALexAn]

Spoiler

Spoiler

I know I have used way too many tools! and the guess, for number of columns needed is burdening my workflow. Will work on optimizing .

[vaishabhinav_iitm]

Spoiler