Aggr of an Aggr

[cjohnson]

Hi Everyone,

I don't think it can be done the way I am trying to do it (which is an aggr in an aggr), but you all have an idea for an alternative way?

So the premise is simple. I have finished goods and components (that make up the finished goods).

Let's say I have 3 finished goods:

1 - X Y Z

2 - X Y Z

3 - X Y G

Please note that finished good 1 and 2 have the same (makeup of) components. These are called a variant. So between those 3  finished goods - there are 2 variants (X Y Z and X Y G). I can't flag these variants in the script because we need to be able to select other attributes to determine what these variants are.

In the end, what I am trying to get is::

Dimension    Expression

1, 2                X Y Z

1                    X Y G

I can get it the other way around with a simple aggr function, but I need to be able to do it as a dimension (and not an expression).

So, what I'm looking for is something like below, but it's invalid:

aggr(F1,aggr(F2,F1))

I can't group these in the script because it needs to be dynamic. It can't be an expression because the real example is more complicated with the components being listed by another dimension.

It can't be done in the script really - unless I do a complicated combination of them all.

Does anyone have any thoughts or ideas on how to solve this?

[Oleg_Troyansky]

In order to use AGGR(), at least the combination of the components ( XYZ and XYG) need to be pre-calculated in the script. AGGR() only allows fields (not expressions) to be used as dimensions.

If the combination of components can be stored in a field called Components, for example, then the Calculated dimension becomes fairly simple:

AGGR(

     Concat(distinct F1, ', ')

     , Components

)

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[cjohnson]

Thanks for your response!

Unfortunately, it needs to be dynamic -- which means that creating a concatenated component field in the script would get complicated. It would need to include all the possible component combinations. What I mean by dynamic - if someone selects a list of component attributes - the appropriate concatenated components should be displayed. Similarly - if someone selects one or many finished good attributes - the appropriate components (and finished goods) should be displayed accordingly.

I saw something in the reference manual that (I think) is similar to what I'm trying to do. It's under the chapter of nested aggregations:

"The possibilities of nesting do not end here. The dimension arguments of the aggr function may of course contain calculated dimensions, which in turn make use of the aggr function. It should however be relatively easy to loose track of what you are doing when passing the third level of aggregation."

Does anyone know what is meant by this clause? On the other hand -- under the definition of the aggr function is the following:

"Each dimension must be a single field. It may not be an expression (calculated dimension)."

Aren't the two clauses contradictory?

[Digvijay_Singh]

Let us start from basic app, based on troyansky‌ suggestion, just putting up sample app, I introduced autonumber, see if that can make difference, I know real example is more complex but let us start from this basic one and improve over.

[sasiparupudi1]

I think the text is talking about the expression fields which can be nested with aggregations but not the dimensions.See below is the definition of aggr function from qlikview help.

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

I am afraid what you want to achieve is possible but you can certainly try it in the script as suggested by  Oleg

hth

Sasi

[jonathandienst]

I am not sure what you are looking for - the concat's in your sample do exatcly what you describe, so its not clear where you want to go from here.

[cjohnson]

I forgot to mention - I actually have a basic application in my original text. It is giving me what I want - but what I would like is for it to be reversed - meaning I want what I have listed as the dimension to be an expression - and what I have listed as an expression to be a dimension (with the appropriate syntax of course). The reason I am hoping to get it reversed is because in the real example - components (among other things) need to be listed in a pivot table by other attributes. In the example I listed in the attachment - all I did was move the columns around to achieve the desired effect.

[cjohnson]

Yes = it can be achieved this way - but this is not dynamic.

Please note what happens in your example when you select Component "X".

Please note what happens in my example (originally attached) when I select component "X". The desired result is the output from my example - which hasn't been done in the script.

Just a clarification - the output that I am looking for is exactly the output that is in the attached example - the only difference is that they need to be switched to better apply to the real example (meaning the expression I have listed I'd like to have as the dimension and vice versa).

[Digvijay_Singh]

I am having personal edition so couldn't look at the original app, if possible can you attach image of the desired output.

[cjohnson]

Script:

LOAD * INLINE [

    F1, F2

    1, X

    2, X

    3, X

    1, Y

    2, Y

    3, Y

    1, Z

    2, Z

    3, G

];

Dimension:

=Aggr(Concat(DISTINCT F2,'',F2),F1)

Expression:

Concat(Distinct F1,' ,')

What I want is for these two to be reversed - I want the concatenated finished goods to be the dimension (i.e. an aggr of an aggr).

Thanks for your help!