topic Re: Return Position of an Element from a List in QlikView

Return Position of an Element from a List

Anonymous — Thu, 09 Jun 2016 13:16:06 GMT

Hi Everyone,

I am attempting to the find the position of a string within a list but can't get the expression right.

I have a combination of unique strings stored in a field called "list". I have another field called "version" which contains a single element from "list". I'd like to find the position of "version" within "list".

Here's how I have it set up:

list = '10.0.1', '10.0.2', '10.0.3', '10.0.4'....

version = '10.0.2'

In this example I want an expression to return 2. I've tried using index() and other string functions but I can't quite get it right.

Thanks!

Michael

Re: Return Position of an Element from a List

trdandamudi — Thu, 09 Jun 2016 13:18:58 GMT

Please take a look at the attached link and you will be able to find the solution:

Re: Using a comma-separated list of values in Pick() function

Hope this helps...

Re: Return Position of an Element from a List

santiago_respane — Thu, 09 Jun 2016 13:29:34 GMT

Hi Michael,

if you want to use index you have to consider:

-the length of the item

-Also it must be a string, not a list.

-As index returns the position in the whole string, it must be divided by the length of the item (in this case 6) and also rounded up with the ceil function

=CEIL(INDEX('10.0.1,10.0.2,10.0.3,10.0.4,10.0.5,10.0.6','10.0.4')/6)

Using a list you can go with the match function, it will give you the position of the item to be found, like this:

=Match('10.0.3','10.0.1','10.0.2','10.0.3','10.0.4')

Hope this helps.

Kind regards,

Re: Return Position of an Element from a List

Anonymous — Thu, 09 Jun 2016 14:10:12 GMT

Thanks for the responses so far but I still cannot make it work.

I'd like to stay away from using index() because the denominator does not have a constant length.

Match() returns a "-" for every value in my chart. I've attached a sample qvw.

Any thoughts about where I'm going wrong?

Thanks again.

Re: Return Position of an Element from a List

shree909 — Thu, 09 Jun 2016 16:10:45 GMT

Hi can you try this

=Pick(Match(index(ver_list,version),1,13,25,37),1,2,3,4)

Re: Return Position of an Element from a List

trdandamudi — Thu, 09 Jun 2016 16:58:20 GMT

May be as attached below: