Dynamic nested string concatenation

[arieidel]

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]

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

[petter]

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

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

[arieidel]

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 · Accepted solution]

Like this?

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

[arieidel]

Thanks, Dani. You really are a scripting Guru!