22 Replies Latest reply: Apr 17, 2018 3:36 AM by Tamil arasu

# Pick ith element from concat or similar

Hi, I wish to pick the i'th element of a concatenated selection

=pick(1, concat( [Field], ', ' ))

I would have thought that I should be possible however, currently the second argument is interpreted as a single argument and not as many.

Is my approach possible and am I just missing something?

• ###### Re: Pick ith element from concat or similar

You can use something like below..

=SubField(Concat(DISTINCT [Field],', '),',',1)

//The above one is for picking 1st item

=SubField(Concat(DISTINCT [Field],', '),',',2)

// This is for 2nd item and so on

• ###### Re: Pick ith element from concat or similar

Hi Christoph,

Try this. You can use distinct inside the concat function, if you have duplicate values in the input field.

=Pick(1, \$(=Concat( Chr(39) & [Field] & Chr(39), ', ')))

• ###### Re: Pick ith element from concat or similar

Yes. This was the combination I was looking for.

Is there a distinction between the two?

• ###### Re: Pick ith element from concat or similar

Sorry. Which two you are asking?

• ###### Re: Pick ith element from concat or similar

=Pick(1, \$(=Concat( Chr(39) & [Field] & Chr(39), ', ')))

vs

=SubField(Concat(DISTINCT [Field],', '),',',1)

• ###### Re: Pick ith element from concat or similar

Iterating on that, I would like to use that further so that I can use the single value in some elaborate expression:

Something like:

=only({\$<[Field]={\$(=SubField(GetFieldSelections( [Field],', ',3),',',1))}>}  [Field])

But I do not understand the parsing of expressions good enough yet.

• ###### Re: Pick ith element from concat or similar

Both will show the same result. If you want to check how it is working, you can create a straight table and leave the dimension tab blank and add your expression like below. This way you can easily identify how the expression is parsing the value and debug your expression if required.

BTW, you have to add single quotes.

=only({\$<[Field]={'\$(=SubField(GetFieldSelections( [Field],', ',3),',',1))'}>}  [Field])

• ###### Re: Pick ith element from concat or similar

Thank you, the placement of char(39) is still a mystery to me.

Interestingly, it appears that I cannot select the second element

=only({\$<[Field]={'\$(=SubField(GetFieldSelections( [Field],', ',3),',',2))'}>}  [Field])

What am I missing here?

• ###### Re: Pick ith element from concat or similar

I think the issue with the space after comma in the Getfieldselections delimiter parameter. To understand this better, please see below screenshot. Have a look at the attached file. You can check easily spot the issue by adding the expression straight table like below .

• ###### Re: Pick ith element from concat or similar

Well Spotted! Thank you

• ###### Re: Pick ith element from concat or similar

No Problem. Have a fantastic day!

• ###### Re: Pick ith element from concat or similar

Christoph Weber wrote:

Thank you, the placement of char(39) is still a mystery to me.

Chr(39) is nothing but a single quote. We have to add single quotes around the selected values so I just used chr(39) here. You may ask why not use single quotes directly. If we use single quotes around the filed name then QlikView will consider that as a Text instead of field name.

https://www.asciitable.com/

Hope this is clear now.

• ###### Re: Pick ith element from concat or similar

Thank you for that explanation.

I know about the effects and ' as well as the use of chr(39).

It is just its application together with the \$-expansion that has me at a loss, sometimes.

• ###### Re: Pick ith element from concat or similar

Agree with you. Sometimes it will confuse us.

• ###### Re: Pick ith element from concat or similar

I appreciate the momentum of this discussion and would like to go a little beyond the scope of the question:

How to get rank positions

I have a small selection and a number field.

Now i want to show the rank of one specific field by somehow extending the following: =aggr(Rank( sum( {1} [Numbers]) ) , [Field])

This is my failing solution, so far.

=aggr(Rank( sum( {1<[Field]={'\$(=SubField(GetFieldSelections( [Field],',',3),',',1))'}>} [Numbers]) ) , [Field])

What am I doing wrong?

• ###### Re: Pick ith element from concat or similar

I'm sorry. Could you please explain with sample data and expected output. It will be really easy for me to provide you some idea. Hope this is fine with you.

• ###### Re: Pick ith element from concat or similar

inline [

Field, Numbers

A , 1

B, 2

C, 3

D, 4

]

Assuming any selection,

I want to show the Rank of Element 'C' in a Textbox (or some other object with similar proeprties)

Basically:

=aggr(Rank( sum( {1<[Field]={'C'}>} [Numbers]) ) , [Field])

This should be 2 (As it has the second highest Number - I hope I apply Rank correctly)

• ###### Re: Pick ith element from concat or similar

You can simply try below expression in textbox,

=Aggr(Rank( sum( {1} [Numbers]) ),Field)

• ###### Re: Pick ith element from concat or similar

Yes, this works when only one is selected.

I want to combine it with a set analysis so that regardless of selection I can show the rank of the ith element

• ###### Re: Pick ith element from concat or similar

Ah got it.

=Aggr(If(Field = '\$(=SubField(GetFieldSelections( Field,',',3),',',2))', Rank(sum({1}[Numbers]))),Field)

• ###### Re: Pick ith element from concat or similar

Exactly, I was just about to post the same thing. Thank you