Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
wale_martins
New Contributor III

RANK WHEN USING PICK MATCH FUNCTION

Hi there,

I want to add a Rank function in the following expression that I'm using as a dimensión in a straight table:

=PICK(MATCH($Field,'TAG_COMPROMISO_SECTOR','TAG_ANALITICAS','TAG_VETSICS','TAG_MERCHANDISING','TAG_RESPONSABILIDAD_SOCIAL'),

       [TAG_COMPROMISO_SECTOR],[TAG_ANALITICAS],[TAG_VETSICS],[TAG_MERCHANDISING],[TAG_RESPONSABILIDAD_SOCIAL])


I'm using that expression as dimension because I want to join different column values in a unique dimensión and count the number of times that appear each value in my main table.


=COUNT (DISTINCT ID_VISITA)

Untitled picture.png

My problem is that I want to Rank the dimensión expression.... for example, only showing the top 5 count values but without using dimensión limits because in some cases I don't have to take into consideration the top 5 values for example...

Any idea would be appreciated.

Wale

1 Solution

Accepted Solutions

Re: RANK WHEN USING PICK MATCH FUNCTION

May be like this

If(Rank(Count(DISTINCT ID_VISITA)) < 6, Count(DISTINCT ID_VISITA))

View solution in original post

4 Replies

Re: RANK WHEN USING PICK MATCH FUNCTION

May be like this

If(Rank(Count(DISTINCT ID_VISITA)) < 6, Count(DISTINCT ID_VISITA))

View solution in original post

wale_martins
New Contributor III

Re: RANK WHEN USING PICK MATCH FUNCTION

Thank you very much sunny! I've been wasting my time trying to do it in my dimensión expression....

Best Regards

Wale

wale_martins
New Contributor III

Re: RANK WHEN USING PICK MATCH FUNCTION

Hi stalwar1‌,

I have another issue with my Rank expression. The problem is that I want to create three columns. 1 column with the top 19 values, another column with the next 19 top values and the last one with the next 19 top values.

Untitled picture.png

Now that I'm using your expression in the expressions tab, I have problems to créate this three columns...

Top 19 values; =IF(RANK(COUNT(DISTINCT ID_VISITA),4) <=19,COUNT(DISTINCT ID_VISITA))

Next 19 values; =IF(RANK(COUNT(DISTINCT ID_VISITA),4) >19,COUNT(DISTINCT ID_VISITA))

Next 38 values; =IF(RANK(COUNT(DISTINCT ID_VISITA),4) >38,COUNT(DISTINCT ID_VISITA))

Untitled picture.png

As you can see, the first column is correct, in the second one only appear 18 values and in the third column do not appear values.

Is there a way to handle that?

Thank you in advance!!

Wale

Re: RANK WHEN USING PICK MATCH FUNCTION

May be use an alternative to calculating the rank here:

Continuous Ranking (no missing Rank) | Qlik Community