topic Re: How do you mimic SQL Dense_Rank function using set analysis? in QlikView

How do you mimic SQL Dense_Rank function using set analysis?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am using the following expression to rank average values by group.

=Only({$<OrganizationID={$(vOrganizationID)}>} aggr(Rank(TOTAL -Avg(value)), group))

This works except for when the average of the value is the same. It assigns the values the same rank but skips numbers after that. Here is an example:

I would like instead have it look like this:

Can anybody help me out with this?

Thanks!

Re: How do you mimic SQL Dense_Rank function using set analysis?

vishsaggi — Thu, 16 Nov 2017 22:36:28 GMT

can you share some sample data to look into?

May be check here if this helps:

Dense Rank in QlikView | Qlik Community

Re: How do you mimic SQL Dense_Rank function using set analysis?

Anonymous — Fri, 17 Nov 2017 15:33:37 GMT

I am updating the expression because the one I posted is incorrect. The correct expression is as follows:

=Only({$<goupID={$(vGroupID)}>} aggr(Rank(TOTAL -Avg(value)), groupID))

Sorry for the confusion.

Re: How do you mimic SQL Dense_Rank function using set analysis?

Anonymous — Fri, 17 Nov 2017 15:38:39 GMT

Here is what the example data would look like. I am calculating the average of the value field and returning the rank of the groupID that is set in the vGroupID variable based on the average.

Load * inline

[

groupID,value

1,100

1,0

1,100

2,0

2,100

3,100

3,0

3,100

4,100

];

Re: How do you mimic SQL Dense_Rank function using set analysis?

vishsaggi — Fri, 17 Nov 2017 16:17:33 GMT

can you share your variable expression?

Re: How do you mimic SQL Dense_Rank function using set analysis?

Anonymous — Fri, 17 Nov 2017 16:22:08 GMT

It is not an expression. It is set in a Web UI and will always match one of the groupID values shown in the example data.

Re: How do you mimic SQL Dense_Rank function using set analysis?

Anonymous — Mon, 27 Nov 2017 15:07:48 GMT

Hey Vishwarath, just wondering if you had any luck solving this issue?

Re: How do you mimic SQL Dense_Rank function using set analysis?

vishsaggi — Mon, 27 Nov 2017 15:28:09 GMT

Hey Sorry was away could not work on this. Will look into it.