How do I group by a integer but take into account both instances of the integer?

damen

My company has a demographic table like the one below where we keep true or false info on borrowers.

When the loan has two borrowers, we create the same loan id twice in our system.

I am working to create a minority table but I have to take into account BOTH borrowers. If either row (1,1 - 3,3 in this case) has a true in it, then I need the new column to say yes.

Right now, I am trying to group on loan ID then writing a CASE statement saying that if any of the values are TRUE then it needs to say 'Yes'

but i am not getting rows 1 or 3 to return a 'Yes' value.

Any suggestions? Do i need to combine all the columns and string out the TRUEs into new columns?

GrantSmith

Ideally you'd use a window function to do this but selecting the MAX of a string isn't available in the Rank & Window function in an ETL. Instead use a group by tile and group based off your loan ID and select the MAX of your T/F field (since T is greater than F alphabetically it'll return TRUE if any one of them is true). You can then do a join tile to join this grouped dataset back to your original dataset based on the loan ID to get the T/F value for the entire loan across all borrowers.

GrantSmith

Ideally you'd use a window function to do this but selecting the MAX of a string isn't available in the Rank & Window function in an ETL. Instead use a group by tile and group based off your loan ID and select the MAX of your T/F field (since T is greater than F alphabetically it'll return TRUE if any one of them is true). You can then do a join tile to join this grouped dataset back to your original dataset based on the loan ID to get the T/F value for the entire loan across all borrowers.