How to I transpose the data below to a single observation

[Carolyne]

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

Member ID	Fill Date	index date	days_supply
603	2/17/2005	2/17/2005	30
603	6/13/2005	2/17/2005	30
603	8/11/2005	2/17/2005	30

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

Member ID	Fill date 1	Fill date 2	Fill Date 3	days supply 1	days supply 2	days supply 3	Start Date	End Date
603	2/17/2005	6/13/2005	8/11/2005	30	30	30	2/17/2005	8/15/2005

Thanks!

[CharlieS · Accepted solution]

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 solution]

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 solution]

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

[Carolyne]

Thank you for your help. 

Follow-up Question: how do I take the last data set:

Member ID	Fill date 1	Fill dAte 2	Fill Date 3	days supply 1	days supply 2	days supply 3	Start Date	End Date
603	2/17/2005	6/13/2005	8/11/2005	30	30	30	2/17/2005	8/15/2005

and transpose: Please see below: This will require me using loops and arrays to find the days of medication coverage for each member and calculates the proportion of covered days in the review period. In this case the covered proportion of days covered is 65 over  180 days or .361

Member ID	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10	Day 11	Day 12	Day 13	Day 14	Day 15	Day 16	Day 17	Day 18	Day 19	Day 20	Day 21	Day 22	Day 23	Day 24	Day 25	Day 26	Day 27	Day 28	Day 29	Day 30
603	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
	Day 31	Day 32	Day 33	Day 34	Day 35	Day 36	Day 37	Day 38	Day 39	Day 40	Day 41	Day 42	Day 43	Day 44	Day 45	Day 46	Day 47	Day 48	Day 49	Day 50	Day 51	Day 52	Day 53	Day 54	Day 55	Day 56	Day 57	Day 58	Day 59	Day 60
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0
	Day 61	Day 62	Day 63	Day 64	Day 65	Day 66	Day 67	Day 68	Day 69	Day 70	Day 71	Day 72	Day 73	Day 74	Day 75	Day 76	Day 77	Day 78	Day 79	Day 80	Day 81	Day 82	Day 83	Day 84	Day 85	Day 86	Day 87	Day 88	Day 89	Day 90
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	Day 91	Day 92	Day 93	Day 94	Day 95	Day 96	Day 97	Day 98	Day 99	Day 100	Day 101	Day 102	Day 103	Day 104	Day 105	Day 106	Day 107	Day 108	Day 109	Day 110	Day 111	Day 112	Day 113	Day 114	Day 115	Day 116	Day 117	Day 118	Day 119	Day 120
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1
	Day 121	Day 122	Day 123	Day 124	Day 125	Day 126	Day 127	Day 128	Day 129	Day 130	Day 131	Day 132	Day 133	Day 134	Day 135	Day 136	Day 137	Day 138	Day 139	Day 140	Day 141	Day 142	Day 143	Day 144	Day 145	Day 146	Day 147	Day 148	Day 149	Day 150
	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0
	Day 151	Day 152	Day 153	Day 154	Day 155	Day 156	Day 157	Day 158	Day 159	Day 160	Day 161	Day 162	Day 163	Day 164	Day 165	Day 166	Day 167	Day 168	Day 169	Day 170	Day 171	Day 172	Day 173	Day 174	Day 175	Day 176	Day 177	Day 178	Day 179	Day 180
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1
	Days Covered	p_days_covered
	65	0.36111

[jeff_reynolds]

So this one should at least point you in the right direction. I added another "member" in order to ensure that my groupings worked as intended.