Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

chrweber
Contributor

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?

1 Solution

Accepted Solutions

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), ', ')))

22 Replies

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), ', ')))

chrweber
Contributor

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?

chrweber
Contributor

Re: Pick ith element from concat or similar

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


vs


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

chrweber
Contributor

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.

Capture.PNG

BTW, you have to add single quotes.

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

Re: Pick ith element from concat or similar

If you got the solution, please close the thread by marking correct and helpful answers.

chrweber
Contributor

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?

Community Browser