topic Re: Dynamic nested string concatenation in QlikView

Dynamic nested string concatenation

arieidel — Mon, 26 Oct 2015 14:06:28 GMT

Hello everyone,

I need to generate a string dynamicaly, based on two dimensions, Group and Subgroup, and a metric Total.

The string should look like this:

Group A

Subgroup A1 | TotalA1

Subgroup A2 | TotalA2

Group B

Subgroup B1 | TotalB1

Subgroup B2 | TotalB2

... and so on.

What I've already got is something like this using CONCAT function:

Group A | Subgroup A1 | TotalA1

Group A | Subgroup A2 | TotalA2

Group B | Subgroup B1 | TotalB1

Group B | Subgroup B2 | TotalB2

But I would like it to be as shown before, so I don't need to repeat the "Group" for each Subgroup.

I know I have to make a nested string concatenation using two CONCAT() functions but the result wasn't satisfactory.

Maybe I should use AGGR() function for the "Group" dimension but I don't know how.

I tried to write something like: AGGR (Group, CONCAT(Subgroup, '|')) but I got this error message:

Error: Error in expression:

Nested aggregation not allowed

Could you please help me?

Thanks,

Ariel

Gysbert_Wassenaar — Mon, 26 Oct 2015 14:28:59 GMT

Can you post a small qlikview document with example data that demonstrates the problem?

petter — Mon, 26 Oct 2015 14:48:13 GMT

This should do the trick - at least it did for me:

=Concat( DISTINCT GroupField & Aggr( Concat( SubGroupField & '|' & TotalField, Chr(10) ) , GroupField) , Chr(10) )

arieidel — Mon, 26 Oct 2015 14:58:09 GMT

Hello Petter,

It almost did it! But is repeating the Group value.

I'm attaching a qvw with the 3 cases: the basic one, the other with this nested error and the suggested by Petter.

Thanks,

Ariel

danielrozental — Mon, 26 Oct 2015 15:04:28 GMT

Like this?

=concat(aggr(concat(distinct Group & '|' & aggr(Concat(total <Group> distinct Subgroup & '|' & Total),Subgroup,Group)),Group))

arieidel — Mon, 26 Oct 2015 15:12:48 GMT

Thanks, Dani. You really are a scripting Guru!