topic Pick ith element from concat or similar in QlikView

Pick ith element from concat or similar

chrweber — Wed, 25 Nov 2020 16:16:04 GMT

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

MK_QSL — Mon, 16 Apr 2018 19:10:04 GMT

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

tamilarasu — Tue, 17 Apr 2018 02:24:33 GMT

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

chrweber — Tue, 17 Apr 2018 03:35:28 GMT

Yes. This was the combination I was looking for.

Is there a distinction between the two?

Re: Pick ith element from concat or similar

tamilarasu — Tue, 17 Apr 2018 03:57:42 GMT

Sorry. Which two you are asking?

Re: Pick ith element from concat or similar

chrweber — Tue, 17 Apr 2018 03:58:52 GMT

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

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

Re: Pick ith element from concat or similar

chrweber — Tue, 17 Apr 2018 04:02:07 GMT

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

tamilarasu — Tue, 17 Apr 2018 04:14:57 GMT

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

tamilarasu — Tue, 17 Apr 2018 05:00:58 GMT

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

Re: Pick ith element from concat or similar

chrweber — Tue, 17 Apr 2018 05:16:53 GMT

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

tamilarasu — Tue, 17 Apr 2018 05:31:28 GMT

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

chrweber — Tue, 17 Apr 2018 05:33:33 GMT

Well Spotted! Thank you

Re: Pick ith element from concat or similar

tamilarasu — Tue, 17 Apr 2018 05:42:21 GMT

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.

Below link may help you to understand about Ascii table.

https://www.asciitable.com/

Hope this is clear now.

Re: Pick ith element from concat or similar

tamilarasu — Tue, 17 Apr 2018 05:42:45 GMT

No Problem. Have a fantastic day!

Re: Pick ith element from concat or similar

chrweber — Tue, 17 Apr 2018 05:45:11 GMT

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

tamilarasu — Tue, 17 Apr 2018 05:55:34 GMT

Agree with you. Sometimes it will confuse us.

Re: Pick ith element from concat or similar

chrweber — Tue, 17 Apr 2018 06:46:57 GMT

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

And want to relate to your answers here:

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

tamilarasu — Tue, 17 Apr 2018 06:56:19 GMT

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

chrweber — Tue, 17 Apr 2018 07:01:25 GMT

Load *

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

tamilarasu — Tue, 17 Apr 2018 07:16:40 GMT

You can simply try below expression in textbox,

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