How to I transpose the data below to a single observation

Question

How do I transpose the data below  to a single observation:

Member IDFill Dateindex date

days_supply6032/17/20052/17/2005306036/13/20052/17/2005306038/11/20052/17/200530

End goal is for the data above to look like this:

Member IDFill date 1Fill date 2Fill Date 3days supply 1days supply 2days supply 3Start DateEnd Date6032/17/20056/13/20058/11/20053030302/17/20058/15/2005

Thanks!

CharlieS · Accepted Answer

I started by using a Tile tool to assign a Record ID to each fill date that is unique for each Member ID. After that, a Transpose and a Cross Tab tool took care of most the heavy lifting.

An example solution is attached.

tcroberts · Accepted Answer

Hi I've put together a sample workflow for you:

Essentially I use some REGEX_Replace to turn each of the fields into a number, and check the value 3 rows ahead (which is the same field, for the previous record) to see what the number should be. If there is no number, it becomes a 1, otherwise you add one.

I then filter out unwanted fields, compute the maximum and minimum fill dates, CrossTab the data back into a horizontal format, and join in the start and end dates on Member ID.

To do this with additional members, just remember to group by MemberID in the Transpose, CrossTab, and MultiRow Formula Tools.

Let me know if this works for your use-case\

Cheers!

jeff_reynolds · Accepted Answer

The attached workflow should work for what you're looking for.