How to create a summary table combining business/analysis collections

[AntR_Upp]

For key performance indicator (KPI) reports within our business (fibre broadband) we need to combine grouped statistics from multiple data tables. For example we need to combine information about new customers (sourced from salesforce) aggregated by town with data summarizing available active connections per town. The goal is to be able to write this back to a postgres database running once a month where all the information is pulled from its respective locations.

A method I thought would work to generate a collection of summary metrics in one table was via analysis collections appending an arbitrary ‘join’ variable to each collection I wanted to bring into the same table (the subsequently joining them).

This has worked some times but not others and I cannot narrow down why at times it does not work. If possible I would appreciate exploring how to resolve this. In case it is an obvious issue here is how it occurs:

Example of Current Process and join

• Filter a business collection to get 'active' commercial buildings
• Flatten to get the total number in each town
• Add a variable to collection with the number 1 to facilitate the join

• Filter a business collection to get active residential buildings
• Flatten to get the total number in each town
• Add a variable to collection with the number 1 to facilitate the join

Select join on the join variable between the 2 collections. Both tables are a single row and should append together on the join variable '1'.

Error:



The error occurs immediately when I try to join these collections.

Solutions attempted:

• Change the variable joined on (tried both string and integer) and try force the type using tointeger/tostring
• Perform the filter on the business collection prior to analysis collection and the analysis collection section – no difference
• Change the order of the analysis collections used in the join

I have successfully joined other tables together using this same approach so I cannot understand why it does not work for certain collections. It's important for automating any summary reports we’ll produce.

Let me know if more information is required, hope you can help

 

[Björn Schaefer]

Hi
As far as I understood: You would like to sum up residental and commercial buildings within a town?
I would solve this within an Analysis with 3 AO tables:

1.) Buildings where attribute = residential
2.) Buildings where attribute = commercial
3.)Towns

I guess, each Building has an attribute joinable to town:

Town join aggegate Buildings residential, additional field: Numbers of Buildings residential, Exportfilter all
this new AO is to join again with Buildings commercial as shown before, so that the resulting AO Table Buildings (3) will show "amount residential" and "amount commercial"