Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

storugglan
Not applicable

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

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...

1 Solution

Accepted Solutions
Not applicable

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

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

7 Replies
Not applicable

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

Do you mean this?

See attached

storugglan
Not applicable

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

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.

YearMonthShelfIDValueIndicator
201302185H
201302175H
201302165H
201302145H
201302135H
201302125M
201302115M
20130216M
20130215M
201302281H
201302271H
201302261H
201302241H
201302231H
201302221M
201302211M
20130221M
201302385H
201302375H
201302365H
201302345H
201302335H
201302325M
201302315M
20130235M
20130232M

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.

Not applicable

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

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

storugglan
Not applicable

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

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.

Not applicable

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

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

storugglan
Not applicable

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

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

Not applicable

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

Happy to hear that