Merge data of 1 id in multiple rows into 1 row

Question

Hello,

I have data coming in which has multiple rows of 1 ID with like 20 columns. Column 11 to column 20 consist of flag

1 row has 1 flag populated from those columns. Thus if there are 3 flags in that ID then I am getting 3 rows for that ID.

All other columns will have whatever values but column 11 will have yes (for example) rest until 20 will remain blank, next entry will have yes in column 12 and rest column 11 and 13-20 will be blank.

And so on....

I need only 1 row for that ID with yes in column 11,12,13 and rest until 20 as blank. And column 1-10 will remain as is as they contain different details and is constant.

ID 1.

ID 1

Each id will have different occurrences.

TheMattLeonard · Answer

It's hard to say without seeing your data, but if I had to guess your solution will look something like this:

1. Transpose Tool - your ID and any columns that are consistent for each ID you will check as your "Key" columns. All of the columns containing the possible flags will be your "Data" Columns

2. Filter Tool - Next use a filter tool tod filter for [Value] is not null

3. Summarize Tool - Group by [ID], [Name], and [Value] (this will get rid of any duplicate rows if your data is set up as such)

4. Crosstab Tool - Group by [ID], Change column header to [Name], and [Value] for values. Select "Concatenate" as your option.

4b. If the crosstab doesnt get you exactly what you want, try using a formula tool to combine [Name] and [Value] and then use another Summarize tool, grouping by [ID], and going to String then Concatenate on your newly created field.

Hopefully this helps or at least gets you going in the right direction

binu_acs · Answer

@ShantanuDagar can you provide a sample input file and expected output file for better understanding?