Forum Discussion

boykin188's avatar
boykin188
Frequent Visitor
7 years ago
Solved

Multiple rows per unique ID

I have a table with multiple rows per unique ID, and some columns which reference "flags" (basically a 1 or  0) that something has occurred relative to that ID. Due to the way the data is structured, there can bemultiple instances of a flag showing up for each ID:

ID      ColA ColB ColC
31      1       1        0
31      0      1        0
31      1       1       0
144     1      0       0
144     1     0       0
144     1     0       0
26      0      0       0
26      0      0       1


But I don't care if the "1" shows up multiple times for each ID --- I want to add three columns which basically indicates, for each ID, whether or not a flag has occurred there ever (as indicated by the "1"). Call them ColA_Flag, ColB_Flag, and ColC_Flag. 


So for ID 31, there would be a "yes" for Col A amd Col B, but a "no" for Col C. 

 

So it would look like this:

 

ID      ColA ColB ColC   FlagA   FlagB  FlagC
31      1       1        0         Yes     Yes       No
31      0      1        0           Yes     Yes      No
31      1       1       0          Yes      Yes     No
144     1      0       0          Yes      No      No
144     1     0       0           Yes      No      No
144     1     0       0           Yes      No      No
26      0      0       0          No      No       Yes
26      0      0       1          No      No       Yes


Any help is appreciated!

  • Anonymous's avatar
    Anonymous
    7 years ago

    You can use Min / Max

     

    FlagA = If(Max(Table1[ColA]) = 1, "Yes", "No")
     
     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    You can use Min / Max

     

    FlagA = If(Max(Table1[ColA]) = 1, "Yes", "No")