topic Re: Index Match in Qlikview? in QlikView

Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 09:06:59 GMT

Hi,

I have UL Count and Target UL Count as dimension , UL Count % as UL Count /Target UL Count and Rank as the rank function.

How do i create a text box where i pull UL count (eg. 70) when Rank = 1?

Thanks!

Re: Index Match in Qlikview?

Anil_Babu_Samineni — Wed, 27 Dec 2017 09:17:32 GMT

May be this?

If(Rank(Sum([UL Count])/Sum([Total UL Count]))=1, [UL Count])

Re: Index Match in Qlikview?

Chanty4u — Wed, 27 Dec 2017 09:18:56 GMT

try this

text box

=[UI Count]&' '& Rank

Re: Index Match in Qlikview?

surendraj — Wed, 27 Dec 2017 09:26:28 GMT

May be This!!

Only({<Rank={1}>}[UI Count])

Re: Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 09:30:45 GMT

It returns blank though.

Do i have to aggr "Rank(Sum([UL Count])/Sum([Total UL Count]))"?

Re: Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 09:31:21 GMT

I need to return only 1st rank though

Re: Index Match in Qlikview?

Anil_Babu_Samineni — Wed, 27 Dec 2017 09:33:41 GMT

How you got [UL Count %] ??

Re: Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 09:35:49 GMT

As Rank is an expression with Rank(total "UL Count"/"Target UL Count"), i tried this instead:

Only({<Rank(total "UL Count"/"Target UL Count")={1}>}[UI Count])

but i got error in set modifier expression

Re: Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 09:36:49 GMT

With this expression - UL Count /Target UL Count

Re: Index Match in Qlikview?

surendraj — Wed, 27 Dec 2017 09:40:10 GMT

I thought you need to do in separate way..first you need to calculate rank then you can try with above expression.it will works.

Re: Index Match in Qlikview?

Anil_Babu_Samineni — Wed, 27 Dec 2017 09:41:45 GMT

If so, My expression should work. any way can you try this?

FirstSortedValue([UL Count],-Aggr((Sum([UL Count])/Sum([Total UL Count])), [UL Count]))

FirstSortedValue([UL Count],Aggr((Sum([UL Count])/Sum([Total UL Count])), [UL Count]))

Re: Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 09:56:17 GMT

Sure. Much thanks for your help.

When i tried this:

FirstSortedValue([UL Count],-Aggr((Sum([UL Count])/Sum([Total UL Count])), [UL Count]))

it returns 84 (YES!) Possible to return 70 instead?

but when i tried this:

FirstSortedValue([UL Count],Aggr((Sum([UL Count])/Sum([Total UL Count])), [UL Count]))

it returns blanks (not sure why)

Re: Index Match in Qlikview?

Anil_Babu_Samineni — Wed, 27 Dec 2017 09:58:36 GMT

May be attach sample

Re: Index Match in Qlikview?

pradosh_thakur — Wed, 27 Dec 2017 11:33:06 GMT

try this

=concat(If(aggr(Rank(Sum([UL Count])/Sum([Total UL Count])),[UL Count])=1, [UL Count]),',')

=concat(If(aggr(Rank([UL Count]/[Total UL Count]),[UL Count])=1, [UL Count]),',')

Re: Index Match in Qlikview?

seahyanzheng — Wed, 27 Dec 2017 11:43:40 GMT

Hi,

=concat(If(aggr(Rank(Sum([UL Count])/Sum([Total UL Count])),[UL Count])=1, [UL Count]),',')

returns rank 29?

=concat(If(aggr(Rank([UL Count]/[Total UL Count]),[UL Count])=1, [UL Count]),',')

returns many UL count concatenate together

Re: Index Match in Qlikview?

pradosh_thakur — Wed, 27 Dec 2017 11:52:16 GMT

=concat(If(aggr(Rank(Sum([UL Count])/Sum([Total UL Count])),[UL Count])=1, [UL Count]),',')

returns rank 29?

you have rank 29 as well ? a screenshot please.

try this as well

=concat(If(aggr(Rank(Sum([UL Count])/Sum([Total UL Count])),[UL Count])=min( total aggr(Rank(Sum([UL Count])/Sum([Total UL Count])),[UL Count])), [UL Count]),',')

Re: Index Match in Qlikview?

sunny_talwar — Wed, 27 Dec 2017 12:38:32 GMT

May be this

FirstSortedValue({<[UL Count] = {">0"}, [Total UL Count] = {">0"}>}[UL Count],Aggr((Sum([UL Count])/Sum([Total UL Count])), [UL Count]))

Re: Index Match in Qlikview?

petter — Wed, 27 Dec 2017 13:18:37 GMT

This one should work for you:

Only( {<R#={"=Rank(TOTAL [UL Count]/[Target UL Count])=1"}>} [UL Count] & ' ' & [Target UL Count] & ' ' & Num([UL Count]/[Target UL Count],'0%'))

If there is a possibility of a shared rank #1 then you would have to do a Concat() instead of Only:

Concat( {<R#={"=Rank(TOTAL [UL Count]/[Target UL Count],1,1)=1"}>} R# & ': ' & [UL Count] & ' ' & [Target UL Count],' / ')

I have added a field named R# in the load script to pick out the row number. It is calculated with the RowNo() function.

LOAD

RowNo() AS R#,

.....

;

Re: Index Match in Qlikview?

seahyanzheng — Thu, 28 Dec 2017 02:18:35 GMT

I tried with sample and above expression works fine but im not sure why my data return 2nd rank instead. Much thanks for your help

Re: Index Match in Qlikview?

seahyanzheng — Thu, 28 Dec 2017 02:21:01 GMT

It returns 2nd rank though. Not sure why..