topic Re: Set analysis to get 1 specific field in dataset in QlikView

Set analysis to get 1 specific field in dataset

Mon, 04 Nov 2013 15:46:06 GMT

Hi,

I have a bookmark filter with the top rank data records.

Since its top ranks, there isnt a way to identify each specific row by an known id.

Is there a way to target specific field in a set analysis by limiting it to a specific row.

I have found RowNo() or RowNo(Total) functions but cant seem to get it to apply in a set analysis for a Text Box..

I tried, this, but its not really valid:

=Only({<RowNo(TOTAL) = {1}>} ENGLISH_TEXT)

Re: Set analysis to get 1 specific field in dataset

swuehl — Mon, 04 Nov 2013 15:49:28 GMT

Well, I haven't fully understood your setting but you can use a search expression in a field modifier, e.g. using something like

=Only({<CustumerID = {"=rank(sum(Amount))=1"} >} ENGLISH_TEXT)

Re: Set analysis to get 1 specific field in dataset

Mon, 04 Nov 2013 15:59:59 GMT

The issue i have is, i don't have a field modifier to work with,

From a data table, i filter the data in a bookmark to have the top 10 based on ranking, these ranks can be identical(tied ranks) so i cannot search rank as a field modifier.

Is there a way to target a specific row of data without know any of the field modifiers?

something in the line of, get rank from row number 3, etc ?

Re: Set analysis to get 1 specific field in dataset

swuehl — Mon, 04 Nov 2013 16:05:09 GMT

Still not sure what you are talking about, as usual an example really helps to understand.

You can create a unique record ID in your load script, then rank this record ID to get the first, second, third record in your selection.

Re: Set analysis to get 1 specific field in dataset

Mon, 04 Nov 2013 17:44:52 GMT

Sorry for the late update, but i think you got the gist of what i'm trying to do.

And your suggestion is workable if i do create a unique identifier, but Im trying to avoid this calculation set due to large volume of data.

But to further elaborate, my original question is whether there is a way to target a specific row of data by only knowing the row number,

For example, with the following table :

Item	Rank	Value	Text
21	1	99	AA
2	2	80	BB
15	2	80	CC
4	3	77	DD
8	4	60	EE

Is there a way to get the text "CC" in a text box object by only knowing it will be the 3rd record row ?

Item number is my unique identifier, but the number is unknown as data will varies, since when filter by top ranks, the rest is omitted. I need to get the text column, but only available information is that the 1st row will be my 1st rank, 2nd row, 2nd rank, etc..

Re: Re: Set analysis to get 1 specific field in dataset

swuehl — Mon, 04 Nov 2013 23:00:15 GMT

Maybe like this?

Re: Set analysis to get 1 specific field in dataset

Mon, 04 Nov 2013 23:16:46 GMT

Yes, that is what i'm trying to do but without slider,

Can you explain the rank() function parameters, I dont seem to have any luck finding the documentation for the function.

=rank(sum(Value),4)

what is the second parameter ( 4 ) used for ?

and how is the sum of all the values being applied to determine the ranking ?

Re: Set analysis to get 1 specific field in dataset

jonathandienst — Tue, 05 Nov 2013 06:15:18 GMT

From the documentation:

rank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other rows containing the current column segment

and returns the ranking of the current row within the segment. For bitmap charts, the current column

segment is defined as it appears in the chart's straight table equivalent (Actually all QlikView charts have a

straight table equivalent with the exception of the pivot table which has a more complex structure).

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment

is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions,

the current column segment will include only rows with the same values as the current row in all dimension

columns except for the column showing the last dimension in the inter field sort order.

The ranking is returned as a dual value, which in the case when each row has a unique ranking will be an

integer between 1 and the number of rows in the current column segment.

In the case where several rows share the same ranking, the text and number representation can be controlled

as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default) If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group.If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group.If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1 Lowest rank on all rows.
2 Average rank on all rows.
3 Highest rank on all rows.
4 Lowest rank on first row, then incremented by one for each row.

The third parameter format specifies the text representation of the function result.

format

0 (default) Low value&' - '&high value on all rows (e.g. 3 - 4).
1 Low value on all rows.
2 Low value on first row, blank on the following rows.

The order of rows for mode 4 and format 2 is determined by the sort order of the chart dimensions.

Examples:

rank( sum( Sales ))
rank( sum( Sales ), 2 )
rank( sum( Sales ), 0, 1 )

Regards

Jonathan

Re: Set analysis to get 1 specific field in dataset

swuehl — Tue, 05 Nov 2013 09:43:27 GMT

Right,

parameter 4 is used to determine how to handle ties (i.e. assign rank 2 & 3 in your example).

sum(Value) is just the expression that determines the rank (it is evaluated per item in the field modifier search expression). In your example, one could also just use rank( Value ,4), because there is only one value per item in the sample data.

Of course you don't need the slider, just use the set expression.