topic Extracting from a GetFieldSelections result in QlikView

Extracting from a GetFieldSelections result

Thu, 28 Apr 2011 17:18:05 GMT

Hi,

I have a list box with some names. It is possible to select more one name. I want to get the first two characters for each selected value in the GetFieldSelections(Name,','). Is it possible?

Thanks

Extracting from a GetFieldSelections result

Anonymous — Thu, 28 Apr 2011 17:38:24 GMT

This is not a perfect solution, but probably gives you an idea.

=left(SubField(GetFieldSelections(Name,','),',',N),2)

Replace N with 1 if you want the first selection, 2 for second selection ans so on. Ideally you would want to loop though to get all.

Regards,

Dinesh.

Extracting from a GetFieldSelections result

Thu, 28 Apr 2011 17:48:53 GMT

Ok, thanks, but I'd like to obtain the dynamic result of your tip.

I'm interesting to get the all values.

Thanks

Extracting from a GetFieldSelections result

rwunderlich — Thu, 28 Apr 2011 19:04:36 GMT

Can you load another linked table that has the two character representations and use that field in your expression?

Extracting from a GetFieldSelections result

johnw — Thu, 28 Apr 2011 23:34:45 GMT

There might be a simpler expression, but this appears to work.

concat({1} if(index(getfieldselections(Name),Name),left(Name,2)),', ')

I assume you really want the selected names, not just the possible names. Possible names are much easier:

concat(left(Name,2),', ')

Extracting from a GetFieldSelections result

Fri, 29 Apr 2011 07:48:01 GMT

Hi John,

I have tried your expression but I have repetead substring of Name field (it is in a list box). I don't understand the use of Name in the Index statement after getfieldselections(Name). In this case, does Name contain the all possible values?

Thanks

Extracting from a GetFieldSelections result

Fri, 29 Apr 2011 10:51:18 GMT

Hi,

I'm solving with a VBScript macro that splits the content of the getfieldselections into an array of strings and then I get the substring for each array element.

I hope to don't use a macro.

Thanks

Extracting from a GetFieldSelections result

Anonymous — Fri, 29 Apr 2011 11:54:19 GMT

To solve the duplicate issue:

concat(distinct if(index(getfieldselections(Name),Name),left(Name,2)),', ')

Extracting from a GetFieldSelections result

Fri, 29 Apr 2011 12:12:56 GMT

Ok, thanks.

It's a good and interesting alternative to the VBScript macro.

Extracting from a GetFieldSelections result

johnw — Fri, 29 Apr 2011 18:58:47 GMT


Dinesh Thoniparambil wrote:concat(distinct if(index(getfieldselections(Name),Name),left(Name,2)),', ')

The removal of the {1} set means that you're now looking at the intersection of selected and possible values, not at all selected values. It is possible to have values selected that are not possible. Assuming you want to actually see ALL the selected values, you need to add that back in.

concat({1} distinct if(index(getfieldselections(Name),Name),left(Name,2)),', ')

As for what's going on, let's say you've selected Andy, Bob and Carla, and not selected Dan or Erin. Let's say you then select value 'Male' in the field "Sex". Only Andy and Bob are now possible, but Andy, Bob and Carla are still selected, so getfieldselections(Name) returns the string 'Andy, Bob, Carla'. Now, concat() is an aggregation function. Think of it as a loop. In this case, we loop through names. Normally, it would just loop through possible names, so it would only loop through Andy and Bob. We also need it to loop through Carla, so we use the {1} set to make sure we loop through all names, not just possible names. So we consider each name in turn. First, Andy. We're doing this: index('Andy, Bob, Carla','Andy'). The substring 'Andy' is indeed in our list, in position 1, which is what the index() function returns. Anything non-zero is considered to be true, so the if() is true, and we go to the next part, left(Name,2). This returns 'An'. We keep looping. 'Bob' is position 7, true, so we return 'Bo'. 'Carla' is position 11, true, so we return 'Ca'. 'Dan' isn't in the string, so we return nothing. 'Erin' isn't in the string, so we return noting. So now we have values 'An', 'Bo' and 'Ca'. Distinct here does nothing since they all start with different two letters, but if we'd selected 'Annie' as well, the distinct would eliminate the duplicate. Finally we concatenate them all together with the string ', ' between each. Final result is 'An, Bo, Ca'.