topic Re: need dynamic index for current selected field value list in QlikView

need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 14:16:20 GMT

how can i get a dynamic index field that assigns the current selection a number in asc order?

like this:

load A,

rowno() as Index_Fix

from table.csv;

this would be the fix index. if i filter and get a subset of rows, the index_fix will display numbers assigned to the corresponding field in the table. Assume i have Num Rows = 100. If i filter in my application, my current selection accounts for,say 23 rows. I want to create a dynamic index, which assigns to the current selection a number in numerical order: 1,2,3,4,5, ... 23.

If i do a second filter of fields and i get as current selection 4 rows, then my dynamic index shall assign to the rows 1,2,3,4

how would this be possible?

thanks for any help

Felipe

Re: need dynamic index for current selected field value list

Gysbert_Wassenaar — Fri, 16 Aug 2013 14:20:43 GMT

Try using just rowno() as expression in your chart, or rowno(total) if your chart has multiple dimensions. Then you can use the Sort option to get the order correct.

Re: need dynamic index for current selected field value list

swuehl — Fri, 16 Aug 2013 14:26:49 GMT

Or if your new index shall be based on Index_Fix, maybe like

=rank( -only(Index_Fix) )

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 14:34:40 GMT

thanks

how would that be in a text object?

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 14:45:39 GMT

the requirement is the following:

from the inital table, A are comments, so every comment is different. B is the contactID and it is linked to other tables in the model which i use as filters.

I need to display all comments in a sequence in a diagram box, in form of a comment diagram. So the only thing that came to my mind, was to sort the filtered comments and to put them in text objects based on the index.

the user shall be able to scroll down the comments to see all filtered ones.

Is this possible at all?

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 14:47:17 GMT

this is clear 🙂 i posted the requirement, this was only a middle step, probably there are easier ones

Re: need dynamic index for current selected field value list

swuehl — Fri, 16 Aug 2013 14:51:38 GMT

Still not sure what you want to achieve, maybe

concat(FieldA, ', ', Index_Fix)

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 15:00:45 GMT

thanks for this

i want to get the filtered comments displayed, one by one, in text boxes, as indicated in the requirement.

the index shall help to sort the comments, but maybe there is another easier way to reach the same final goal.

Re: need dynamic index for current selected field value list

swuehl — Fri, 16 Aug 2013 15:08:00 GMT

I personally would not use text boxes, but if that's your requirement.

Create as many text boxes as possibly needed.

In each textbox, create an expression like

=only({<Index_Fix = {1} >} FieldA)

=only({<Index_Fix = {2} >} FieldA)

=only({<Index_Fix = {3} >} FieldA)

=only({<Index_Fix = {4} >} FieldA)

...

and maybe use a show condition for your text box to hide text boxes not needed.

edit:

Or maybe this is a better idea:

If your number of lines / text boxes is limited to say 10 after selection, create 10 text boxes with an expression like

=FirstSortedValue( FieldA, Index_Fix, 1)

=FirstSortedValue( FieldA, Index_Fix, 2)

=FirstSortedValue( FieldA, Index_Fix, 3)

=FirstSortedValue( FieldA, Index_Fix, 4)

...

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 15:21:44 GMT

i attached a doc

i filtered already and want to display one by one the text content in a different text box. Text boxes shall vary according to the selection

what would recommend as an easiest way to display the text list? (the comment diagramm is because they want this to look nice)

thanks

Re: need dynamic index for current selected field value list

swuehl — Fri, 16 Aug 2013 15:28:32 GMT

Here is a sample of my latest suggested approach (FirstSortedValue() and show conditions).

Re: need dynamic index for current selected field value list

Gysbert_Wassenaar — Fri, 16 Aug 2013 15:34:35 GMT

See attached example for another option using one text box to display the texts.

Re: need dynamic index for current selected field value list

swuehl — Fri, 16 Aug 2013 15:43:29 GMT

Nice idea, I am a little fan of the valueloop() function.

A more traditional way to do the same might be (and also getting the sorting right):

=concat( aggr(rank(-index_fix) & ' - ' & text ,index_fix), chr(10))

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 15:47:47 GMT

thanks, this is really good, goes in line with the requirement, but it seems it is not practical for a big number of comments, it is working well, it will be very tedius with more than 2,000 comments.

Re: need dynamic index for current selected field value list

Gysbert_Wassenaar — Fri, 16 Aug 2013 15:49:15 GMT

Thanks! Yeah, that's a lot simpler. But index_fix is fixed. I thought the idea was to generate the index based on the number of selections and always starting from 1. Though the sorting then is hopeless of course

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 15:52:52 GMT

thanks, this is very practical, even though like as what the requirments owner wanted

easy to display 100s of comments

Re: need dynamic index for current selected field value list

swuehl — Fri, 16 Aug 2013 15:54:02 GMT

But index_fix is fixed. I thought the idea was to generate the index based on the number of selections and always starting from 1.

Yes, I think the rank() should take care of that. Attached your modified sample.

Re: need dynamic index for current selected field value list

Gysbert_Wassenaar — Fri, 16 Aug 2013 15:59:03 GMT

Actually, I don't see why you can't use a straight table with some formatting to make it look like a text box. See attached example (the yellow object on the left).

Re: need dynamic index for current selected field value list

Gysbert_Wassenaar — Fri, 16 Aug 2013 16:00:18 GMT

Ah, of course. Silly me.

Re: need dynamic index for current selected field value list

felcar2013 — Fri, 16 Aug 2013 16:08:48 GMT

perfect, the table is better, it is just that the req owner wanted each comment in a "comments diagram", which is an image (.png)

that was the reason

thanks again