topic Re: RANK WHEN USING PICK MATCH FUNCTION in QlikView

RANK WHEN USING PICK MATCH FUNCTION

wale_martins — Tue, 29 Aug 2017 11:29:51 GMT

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)

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

Re: RANK WHEN USING PICK MATCH FUNCTION

sunny_talwar — Tue, 29 Aug 2017 11:55:41 GMT

May be like this

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

Re: RANK WHEN USING PICK MATCH FUNCTION

wale_martins — Tue, 29 Aug 2017 12:45:20 GMT

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

Best Regards

Wale

Re: RANK WHEN USING PICK MATCH FUNCTION

wale_martins — Wed, 30 Aug 2017 09:04:28 GMT

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.

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

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

sunny_talwar — Wed, 30 Aug 2017 11:24:38 GMT

May be use an alternative to calculating the rank here:

Continuous Ranking (no missing Rank) | Qlik Community