topic Straight table-aggr function in QlikView

Straight table-aggr function

Wed, 11 Feb 2015 15:49:02 GMT

Hi Below is my source table

CallID	ID	Name	Contact No
8467	A1	Jeff	1234567890
3456	A1	Jeff	1234567890
6789	A1	Jeff	1234567890
1232	A1	Jeff	2345678901
3234	A1	Jeff	2345678901
5453	A1	Jeff	5678901234
7865	A2	Lynn	4567890123
5463	A2	Lynn	4567890123
9876	A2	Lynn	4567890123
7656	A2	Lynn	8901234567
8329	A2	Lynn	8901234567
7754	A2	Lynn	3456789012
1234	A3	Mark	7890123456
5434	A3	Mark	7890123456
6767	A3	Mark	7890123456
5644	A3	Mark	9012345678
7621	A3	Mark	9012345678
6750	A3	Mark	6789012345

I need to create a straight table with top 3 contact numbers for each combination if ID and Name based on count of CallID

Example if I select Name as Jeff

ID	Name	1	2	3
A1	Jeff	1234567890	2345678901	5678901234

I'm using following expressions

if(aggr(rank(Count(CallID))=1,ID,Name,[Contact No])=-1,[Contact No])

if(aggr(rank(Count(CallID))=2,ID,Name,[Contact No])=-1,[Contact No])

if(aggr(rank(Count(CallID))=3,ID,Name,[Contact No])=-0,[Contact No])

But my final result is currently blank

Attaching source data and qv application.

Re: Straight table-aggr function

giakoum — Wed, 11 Feb 2015 15:53:59 GMT

you need the following function. Note that you need it 3 times, with n=1 2 or 3 to get the 3 values and count(CallID) as sort weight

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

Re: Straight table-aggr function

Wed, 11 Feb 2015 16:16:04 GMT

I tried adding above expression but it returns null value

FIRSTSORTEDVALUE(aggr([Contact No],ID,Name),Count(CallID),1)

FIRSTSORTEDVALUE(aggr([Contact No],ID,Name),Count(CallID),2)

FIRSTSORTEDVALUE(aggr([Contact No],ID,Name),Count(CallID

),3)

Re: Straight table-aggr function

giakoum — Wed, 11 Feb 2015 16:17:51 GMT

I think that Count(CallID) cannot be used as sort weight.

Try calculating it in the reload script as a new field

Re: Straight table-aggr function

anbu1984 — Wed, 11 Feb 2015 16:29:54 GMT

Check this

Re: Straight table-aggr function

Wed, 11 Feb 2015 16:37:23 GMT

Thanks a lot Anbu it worked...