Index Match in Qlikview?

[seahyanzheng]

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!

[Anil_Babu_Samineni · Accepted solution]

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

OR

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

[seahyanzheng]

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)

[Anil_Babu_Samineni]

May be attach sample

[pradosh_thakur]

try this

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

or

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

[seahyanzheng]

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

[pradosh_thakur]

=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]),',')

[sunny_talwar]

May be this

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

[petter]

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#,

.....

.....

;

[seahyanzheng]

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

[seahyanzheng]

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