topic Re: Straight Table Concatenated Text in QlikView

Straight Table Concatenated Text

Anonymous — Thu, 18 Jun 2015 15:07:31 GMT

Hello,

Looking for some help on creating a similar view to Excel. The user is looking for me to concatenate all Possible perils attributed to a loss. If loss # 1-10 is shown on a table, they want to see the perils associated with each similar to the picture below:

Is this possible with some sort of pick function? Just not sure how to properly set this up in a pivot table or a straight table.

Here are the full list of perils:

Thank you,

Justin

Re: Straight Table Concatenated Text

robert_mika — Thu, 18 Jun 2015 15:12:15 GMT

Could you post full data set?

(and not the screen shots)

Re: Straight Table Concatenated Text

Anonymous — Thu, 18 Jun 2015 15:15:16 GMT

I am not sure what you really want, but the concat function migth work if you select the perils which apply

=concat(PERIL,',')

Re: Straight Table Concatenated Text

Anonymous — Thu, 18 Jun 2015 15:23:53 GMT

Rudolf,

Thanks for the help, I had forgot the comma, and also distinct before Peril. Many thanks for sparking the correct answer. Is there a way to limit how many perils show up? Possibly in highest loss order?

Thanks,

Justin

Re: Straight Table Concatenated Text

robert_mika — Thu, 18 Jun 2015 15:42:58 GMT

Try

=CONCAT(IF(aggr(Rank(sum(Loss)),Peril)<=3,Peril),',')

Gives first 3 with the highest values

Feeling hungry:

How To /Missing Manual(17 articles)

Re: Straight Table Concatenated Text

Anonymous — Thu, 18 Jun 2015 15:43:02 GMT

as far as i know it is not possible to Limit the numers.

only if you select only 3 fields, These fields will be concatenated

sorting can be done using the sort tab

what might work is the function findoneof (Returns the number of Position)

findoneof(concat(PERIL,','),',',3) Returns the Position of the third sign ","

then

left(concat(PERIL,','),findoneof(concat(PERIL,','),',',3)-1)

I didnot try it, maybe you have to adjust a Little but the way migth work

Re: Straight Table Concatenated Text

Anonymous — Thu, 18 Jun 2015 17:16:12 GMT

Robert,

The expression last given that is CONCAT(IF(aggr(Rank(sum([CAT AY INC LOSS])),PERIL)<=3,PERIL),',') shows the following result:

If you select one of the dimension values in this case [CAT CODE], it will show the top 3. Is there a way to get this to show without any selections, ie to have the table fully filled out by the top 3?

Thanks again,

Justin

Re: Straight Table Concatenated Text

Anonymous — Thu, 18 Jun 2015 17:18:34 GMT

Robert,

The missing manual link is fabulous as well, great work and thank you.