Forum Discussion

DJBAJG's avatar
DJBAJG
Helper IV
2 years ago

Custom Column identifying a max value in another column

I've got a very large dataset with millions of rows. Within that dataset there are rows with specific combinations of data that are duplicitive. Specifically, a combination of 4 columns, which I need to report on, create duplicate rows. I need to identify these rows, which I have done, and then create another calculated column the displays an indicator (A "1" or "Yes" or anything) telling my which duplicate row has the highest value in another column, one not used in the 4 column combination.

 

Speficially, look at the attachement.

 

The column "Plan Duplicate (Concatenation)" contains the combination of the 4 columns in question. The column "Plan Duplicate (Duplicate Count)" shows the number of duplcate rows. What I want in the "Plan Duplicate (MAX Indicator) column is an indicator that populates only for the duplicate row with the highest "Enrolled Lives" value. So row 2 and 4 in the picture shold have a "1" and rows 1 and 3 should have a "0".

 

Keep in mind the dataset contains millions of rows and the duplicate count could be 2 or 20 or 200 so any hardcoding of duplicate counts is not possible. Also, this needs to function at the "Plan Duplicate(Concatenation)" level so any usage of MAX has to take that into account or else we'll be looking at the MAX of the entire million rows and getting erroneous values.

 

Here's what I've tried so far:

 

Plan Duplicate (MAX Indicator) = IF(
    ajg_ratetiervalue_partitioned[Plan Duplicate (Duplicate Count)] = 1, 1,
    IF(
        ajg_ratetiervalue_partitioned[Plan Duplicate (Duplicate Count)] > 1 &&
        COUNTROWS(FILTER(ajg_ratetiervalue_partitioned,
            EARLIER(ajg_ratetiervalue_partitioned[Enrolled Lives]) > ajg_ratetiervalue_partitioned[Enrolled Lives])),1,0))
 
This get's me the values you see in the picture of 1 on all rows which obviously isn't what I want but I fee like I need to utilize the EARLIER function some how but perhaps I'm way off. 
 
Really need help with this one. Been working on it for a few days now with not much progress.
 
Thanks.