Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
wjtam206
Contributor
Contributor

Rankings

Hello,

I am currently trying to create a table with the top 3 ranking domains as a dimension in my straight table.

My tables currently look like this:

Ranking Table

KeywordDomainRank
Keyword ADomain A1
Keyword A
Domain B
2
Keyword A
Domain C
3
Keyword A
Domain D
4
Keyword A
Domain E
5
Keyword BDomain B1
Keyword BDomain D2
Keyword BDomain A3

Keyword Metric Table

KeywordTrafficCost Per Click
Keyword A50,000$45
Keyword B30,000$7
Keyword C15,000$18
Keyword D18,000$12

The desired output should be:

KeywordTrafficCost Per Click#1 Ranking#2 Ranking#3 Ranking
Keyword A50,000$45Domain ADomain BDomain C
Keyword B30,000$7Domain BDomain DDomain A
Keyword C15,000$18Domain CDomain BDomain E
Keyword D18,000$12Domain DDomain EDomain A

The #1,#2,& #3 ranking column refers back to the rank shown in the ranking table above. Is it possible to show this in a straight table using calculated dimensions?

1 Solution

Accepted Solutions
raman_rastogi
Partner
Partner

Hi Wesly,

Something like this.

Expressions-

FirstSortedValue(Domain,Rank)

FirstSortedValue(Domain,Rank,2)

FirstSortedValue(Domain,Rank,3)

Best

Raman Rastogi

View solution in original post

8 Replies
raman_rastogi
Partner
Partner

Hi Wesly,

Something like this.

Expressions-

FirstSortedValue(Domain,Rank)

FirstSortedValue(Domain,Rank,2)

FirstSortedValue(Domain,Rank,3)

Best

Raman Rastogi

View solution in original post

wjtam206
Contributor
Contributor

Thank you Raman!

I have another question. What if there is a keyword that have domains ranked 1 & 3 but there is no rank 2.

For example:

KeywordDomainRank
Keyword CDomain A1
Keyword C
Domain B
3
Keyword C
Domain C
4

If I use firstsortedvalue in the table it will show as this:

KeywordTrafficCost Per Click#1 Ranking#2 Ranking#3 Ranking
Keyword C15,000$18Domain ADomain BDomain C

Is there a way to show the '#2 Ranking' as null since there is no Rank 2?

vishsaggi
Champion III
Champion III

may be you need something like this?

If yes share some sample data with all the respective values in the ranking will work on it. The below is just unfinished version of Pivot table.

Capture.PNG

wjtam206
Contributor
Contributor

Hi Vishwarath,

I believe that is my desired output in this situation. Was there a specific formula you used to achieve this output?


vishsaggi
Champion III
Champion III

I have done following changes using Pivot table like add

dims:

Keyword

Traffic

= Aggr(Sum([Cost per Click]), Keyword)

Rank

Exprs:

Firstsortedvalue(Domain, Rank)

Then uncheck supress 0 values in presentation tab. And drag your rank field to top of expr.

wjtam206
Contributor
Contributor

Thank you! How would you limit the ranks to only show only 1, 2, 3 and exclude the rest?

wjtam206
Contributor
Contributor

Also is it possible to do this on a straight table as well?

vishsaggi
Champion III
Champion III

Straight table it is going to be manual right coz you have to create expression based on number of ranking values you have. Not sure in straight table how you do it dynamically. So let me know if Pivot works for you. And based on selection of keyword those ranks will be hidden accordingly.