nested aggr to simulate a multi-dimensional chart

[#1]

Hi,

I have some data describing flows of commodities between different geographic areas.

There are 4 fields in my document data:

* start_area: where the commodities leave from?

* destination_area: where the commodities arrive at? (destination_area and start_area share the same values)

* transport_type: how the commodities are transported?

* amount: how many commodities are transported?

For example, I have this row in my source data:

A043     A717     train     80

It means that 80 unities were moved by train from area A043 to area A717.

There can be several destination and transport types for a given starting area.

I have a webservice available which can display those flows on a map. In order to render them properly, it has to been fed with data in the following format for every start_area:

destination_area:amount,transport_type|destination_area:amount,transport_type|destination_area:amount,transport_type

For example, suppose I have the following rows in my source data for flows originating from area A043:

A043     A717     train     80

A043     A211     plane     200

A043     A211     train     60

A043     A349     train     110

To render the flows from A043 on a map I have to send this to the web service:

A717:80,train|A211:200,plane|A211:60,train|A349:110,train

I started implementing an extension to send the proper data to the web service. The object dimension is start_area, but I am struggling to find how to define the expression to have my data properly formatted.

I think I have to use the aggr function 3 times in my expression, to make 3 nested groups, first by grouping by start_area, then for every start_area group by destination_area, then for every destination_area group by transport_type, then for every distinct (start_area, destination_area, transport_type) tuple evaluate the "sum(amount)" expression.

Is it the right way to do it?

Is it possible to simulate a multi-dimensional objects by nesting aggr?

How my expression should be written?

Any help on this topic will be much appreciated!

[Gysbert_Wassenaar]

Try this in the script. That's the best place to calculate these things.

Temp:

load start_area, destination_area, transport, sum(amount) as sum_amount

from ...mysource... group by start_area, destination_area, transport;

Table1:

Load

start_area,

concat(destination_area&':'&sum_amount&','transport_type,'|') as output4webservice

from Temp group by start_area;

[#3]

Thanks for your answer Gysbert. However my requirement is to avoid pre-calculations in the load script, in order to be able to update the map by doing selection in the qlikview document, while your solution always give the same result without relation to the current selection state.

Is there a way to dynamically generate the properly formatted input to feed my webservice?

I can achieve what I want with an extension which has start_area, destination_area and transport_type as dimensions, and manually aggregate the data rows in the extension code to create the formatted output, but I also need to get another expression to be evaluated for every start_area so I cannot use a multi-dimensional object.

[#4 · Accepted solution]

I've just discovered that it is possible to give more than one dimension to the aggr function!

So if my object has start_area as its dimension, the expression to get the formatted output is simply:

concat(

     aggr(destination_area & ':' & sum(amount) & ',' & transport_type,

          start_area, destination_area, transport_type)

     , '|')

I was trying to nest aggr with a different field in every nesting.... This is much simple and give the results I want.