Transform data to binary values

[Carolyne]

Hi: How would I transform my data from:

Readmits	MemberID	Sum Allowable Amount	Age Bands	DRG Desc
0	1	50	75-84	Headache
0	1	50	75-84	Headache
1	2	30	85+	Chest Pains
1	2	30	85+	Chest Pains
1	3	10	65-74	Cough
1	4	5	65-74	Fever

To 

Readmits	MemberID	Allowable Amount	Age Bands_65-74	Age Bands_75-84	Age Bands_85+	**bleep**e Bands_<65	Headache	Chest Pains	Cough	Fever
0	1	50	0	1	0	0	1	0	0	0
1	2	30	0	0	1	0	0	1	0	0
0	3	10	1	0	0	0	0	0	1	0
0	4	5	1	0	0	0	0	0	0	1

Thanks.

[CharlieS · Accepted solution]

Check out the example solution attached and let me know if that works for you. The only downfall is that it doesn't create the "<65" column since that was unknown to the input data.

- RecordID's keep things organized

- Dynamic Select tools sort out what need to be transformed. 

- Transposing allows unique name-value pairs to be identified.

- Cross Tab on these values will create the complete data frame

- Multi Field Formula fills in nulls with 0s.

- RecordID is used to join back to the numeric fields. 

[ponraj · Accepted solution]

Attaching a sample workflow.  Hope this is helpful.