How do I mark TOP/BOTTOM 10 entrys in a table with multiple dimensions

[Anonymous]

Hello everyone.

I have this table:

StuffOfInterest:

( YearMonth, ShelfID, Value)

After loading it in the script, I want to add a field called Priority to the table.

That field should just say 'H' (as in high) for the 10 entrys with highest value for each YearMonth and shelf.

How do I manage that? I have no problem in selecting the top 10 entrys for just YearMonth, but as I said:
I want this top-10 marking for YearMonth AND ShelfID.

Can someone in this community enlighten me?

Then it would also be great if it could read 'L' (as in Low) for the 10 entrys with lowest value, and 'M' (as in Medium) for every other entry.

If that would be doable it would be even more great.

Hoping for insight...

[#2]

Do you mean this?

See attached

[Anonymous]

Hi, and thanks for your imput.

I can follow your thought, but your example doesn't really do the trick.

Maybe I was unable to express my problem. I will try to clarify.

What I want is for the Indicator, for every YearMonth, and every shelfID, mark the entrys
with the top 10 vaues with a 'H'. The rest of the entrys may be marked with indicator=void(),
if that makes things easier. If the ShelfID has only 10 entrys or less, all entrys should be
marked with indicator='H'. In the example below i used top-5 instead of 10, just to show
the idea.

YearMonth	ShelfID	Value	Indicator
201302	1	85	H
201302	1	75	H
201302	1	65	H
201302	1	45	H
201302	1	35	H
201302	1	25	M
201302	1	15	M
201302	1	6	M
201302	1	5	M
201302	2	81	H
201302	2	71	H
201302	2	61	H
201302	2	41	H
201302	2	31	H
201302	2	21	M
201302	2	11	M
201302	2	1	M
201302	3	85	H
201302	3	75	H
201302	3	65	H
201302	3	45	H
201302	3	35	H
201302	3	25	M
201302	3	15	M
201302	3	5	M
201302	3	2	M

If the solution as easily can mark the bottom 10 entrys with a 'L' and all the entrys therebetween with 'M', that's a nice bonus, but what I really need is the top-10 marking in the field Indicator.

[#4]

Sorry, i got u wrong. Let's try again

[Anonymous]

Hi.

I really appreciate your efforts (I have to admit I lack knowledge about the Peek-function, I will study more about it to be sure) but this updated file doesn't really cut it either.

I modified the first data-loading-part to generate more data, to show you the problem:

Insert this code instead of the first INLINE statement:

StuffOfInterest_initial:
LOAD Null() as YearMonth, Null() as ShelfID, Null() as Value AutoGenerate(1);

FOR YM = 201301 TO 201306 STEP 1

FOR SID = 1 TO 10 STEP 1

  FOR ValueCounter = 1 TO Round(Time(Today(1))/(Pow(Rand(),Rand())*1000)) STEP 1
  
   CONCATENATE (StuffOfInterest_initial) LOAD
    $(YM) as YearMonth,
    $(SID) as ShelfID,
    Round(Rand()*(Today(1)/Rand())) as Value
   AutoGenerate(1);
  
  NEXT ValueCounter;
NEXT SID;
NEXT YM;

If you look at the new result, Yearmonth 201301 we will get 10 H, 10 L and a lot of M.

So far, so good. The problem is that I want 100 H and 100 L and (the rest) M.

I want EVERY ShelfID for EVERY YearMonth to have 10 H's and 10 L's (and M for the rest).
And if one shelf would have less then 10 values, then all should be H's.

[#6 · Accepted solution]

I am so sorry, I couldnt understand what your task was. But I hope I finally got it. Check this

[Anonymous]

That was *exactly* what I meant. Wow. Great work, really! Many thanks, much appriciated! 🙂

[#8]

Happy to hear that