topic nested aggr to simulate a multi-dimensional chart in QlikView

nested aggr to simulate a multi-dimensional chart

Mon, 18 Feb 2013 14:51:35 GMT

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!

Re: nested aggr to simulate a multi-dimensional chart

Gysbert_Wassenaar — Mon, 18 Feb 2013 15:20:43 GMT

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;

Re: nested aggr to simulate a multi-dimensional chart

Mon, 18 Feb 2013 15:32:53 GMT

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.

Re: nested aggr to simulate a multi-dimensional chart

Mon, 18 Feb 2013 16:50:36 GMT

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.