Top 10 extended for dups

[CScott]

I have a dataset of 1000 records that I am isolating the Top 5% and next 5% based on the locations current rank between (1-1000) and the locations current percentage.  Since the top 5% would be the top 50 locations, I have that through use of the MultiRow formula and marked them as 1's.  The issues is if at location 50 though location 58 the location percentage is the same even though the rank is increasing.  I want to count those as well in the top 5% but I cant get the multiRow formula to go past one below to identify it as the same. 

[ggruccio]

HI @CScott,

I would try doing a Summarize - Group By or a Unique on the field you want to calculate the rankings on...then run your ranking logic, then join the ranks back to the original dataset.

For example, you have a 1000 row sample with maybe only 800 unique values.  Complete the ranking on the 800 unique values, then join back to your 1000 row sample.  

Will this give you the output you are looking for?

[CScott]

not sure that is what I was looking for.  here is a sample look of the data.  what i want is to meet what is in the Wanted Rank column where the ranking of 1's extends down farther due to the percentage being the same.  So in this example below, top 5 in Orig Rank shows where it would end at just 5  but the issue is that the Pct is the same at 10% for others beyond and should be included as a rank of 1 also.

Location	Rating	Pct	Orig Rank	wanted rank
A	20	5%	1	1	top 5
b	20	5%	1	1	top 5
c	20	5%	1	1	top 5
d	20	10%	1	1	top 5
e	20	10%	1	1	top 5
f	30	10%	2	1	Extended for dup %
g	30	10%	2	1	Extended for dup %
h	30	10%	2	1	Extended for dup %
i	30	15%	2	2	move to next 5
j	30	20%	2	2	move to next 5

[ggruccio · Accepted solution]

Got it!

I assume records i and j would then be combined in the next group of records to make another group of 5 and similar logic would be applied below so that if records below matched the same value they would be grouped with that 5 as well.

I've created an iterative macro that accomplishes this (see the attached file with macro embedded)

This appears to match the output you are looking for:

[CScott]

this looks like it will work perfectly, Thanks.  Now I just need to spend time to understand the Iterative build. 

If I have several containers that I will be doing this process on.  If they are all generic headings, can I call each of them to this same macro or do I need to duplicate it for each attribute and point to separate macros?

[ggruccio]

Hi @CScott,

Glad to have helped!

The macro as-built is expecting these fields...

Location

Rating

Pct

Orig Rank

As long as the additional data sources have the same generic headings you should be able to use it.   Orig Rank may be something that you want to drop in the future, but since the macro is expecting it, you can leave the values blank...

The macro also assumes the dataset is sorted based on the field you want to do the rankings on.  

If you right-click on the Iterative macro you should be able to see the logic that I'm using.  Basically it takes the top 5 records, does a match against the remaining records to see if there are any other values that belong in the top 5 and passes these thru to output.  Then sends the remaining records back through the iterative process.   You should be able to run it with it open to see how the dataset flows thru....

It is also set to max out at 100 iterations - this is something that is easy to change if needed.