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

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?

If this helps, feel free to agree, accept or awesome it!

Tagged:

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**