topic Re: Sorting within set analysis expresssion? in QlikView

Sorting within set analysis expresssion?

Wed, 06 Nov 2013 19:24:12 GMT

Sorry to re-open but I'm still stuck on this a bit.

The sample you provided taught me a lot but giving a scenario where the sample table is being generated by a set analysis in the text box instead of being the raw table data. Is there a way to sort the data by certain columns, within the textbox set analysis expression?

: Assuming that the actual data table is much large.

Type	Item	Rank	Value	Text	Break
A	21	1	99	AA	5
A	2	2	80	BB	5
B	21	1	88	AA	7
B	2	3	85	BB	8
A	15	2	80	CC	6
A	4	3	77	DD	5
A	8	4	60	EE	8

Using set analysis filter by <Type = {'A'}>

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

Inside a text box expression, the following

=only({<Type = {'A'}, Item = {"=rank(sum(Only(<{Type = {'A'}}>Value)),4)=2"}>}Text)

should give me the result : BB

The question:

What if i want the sorting to be by column 'Break' when there is a tie breaker in the 'Value' column.

ie. when both Item 2 and Item 15 have the same Value 80, i want the higher Break to be on top.

Is there a way, using set analysis to make working table data be sort like this ?

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

I'm expecting the Text Box to show the Text 'CC' for rank() = 2.

Re: Sorting within set analysis expresssion?

Wed, 06 Nov 2013 20:50:16 GMT

Try this

=SubField(Concat({<Type={'A'},Rank={2}>}Text,',',-Break),',',1)

Re: Sorting within set analysis expresssion?

Wed, 06 Nov 2013 21:29:39 GMT

Thanks, Devang, still new to qlikview, though I figure the concept behind your code,

but that wont work for me, since its a static code.

For my situation, the data is unknown and handling tie breakers manually like that isnt possible, since i cannot determine when there will be a tie in ranks. It will not be possible to know which field to subField by, 1st, 2nd , 3rd, etc.

Re: Sorting within set analysis expresssion?

Thu, 07 Nov 2013 14:03:44 GMT

This always will give you Text with highest Break. If you want to display in text box, what is your specific requirement.

Sample output with each Type,rank,text, break combination so that someone will be able to help you.

Re: Sorting within set analysis expresssion?

Thu, 07 Nov 2013 14:47:29 GMT

i'm trying to retrieve the text of all the top ranks regardless of ties, so for this sample, 5 text boxes of the top 5 rank text.

After filtering by type, Item number is the unique row identifier sorted by Rank to get the text.

The output sorts by Alpha default on the text, since that is the output.

The output im expecting is the last sample table i listed.

txtbox1 --> AA

txtbox1 --> CC

txtbox1 --> BB

txtbox1 --> DD

txtbox1 --> EE

Re: Sorting within set analysis expresssion?

Thu, 07 Nov 2013 19:10:31 GMT

hi Jun,

You can create a sorting column in the sample table based on the other columns in a table in which you get a unique row identifier and you can use rank function on the sorted column.

Thanks

Nandkishor Suresh Pandirkar

Re: Sorting within set analysis expresssion?

Thu, 07 Nov 2013 19:23:45 GMT

is there no alternative to this ?

I'm trying to avoid this due to large data table and specific data set ranking.

The current file im working with has 250K+ rows with 30+ columns and of these, about everything 50~70 rows is a subset of data by itself that can be rank sorted. And based on the ranking type, the same set of data can be ranked differently based on a different view.

Pre-calculating everything wouldn't be efficient, since not everything requires rank sorting.