GSA 5.2.7. Joining analytic objects by "highest number"

[Björn Schaefer]

Hi everybody!

I'd like to solve the follwing problem, but unfortunately I have no idea, how to....

There are two classes to join by the params shown below:


This leads in some cases to multiple results, so I have to grab the right one by another parameter:

within the 1:n child class, there is a number available

and I need to select / filter the one with the highest number (in Addition to the params shown above).

Is there any easy solution for that?

Many thanks in advance...

 

[Frank Pistorius]

Hi Björn,

This is possible by using the sorting possibilities in the join collection.

I've got a simple example setup using excel files. In this case, there are two tables: main and sub.



From these input collections, I've created two business collections: main and sub.

These two collections are the base collections in my analysis:



To show the different possibilities in setting things up, I've created three different join collections.

1. The first collection is a simple join between the main and the sub-collection with an additional field taken from the sub.


This shows the repeating behaviour that you've shown in your example.

2. The second collection is a join collection, but in this case, sorting the (joined) subs for each main and only picking the first one.

Note that the ordering of the subs is done on the ID field and the ordering is ascending.
By indicating that only one record must be yielded of the sorted elements (for each main), this will only yield the first joined one.

3. The third collection uses the sorting, but in this case in descending order


In this case, the ordering is set to descending. This means that the joined subs will be sorted in descending order (with respect to ID) and 1 element of this set is taken into account.

This translates to your case by:

- set the join to multiple elements
- switch the sorting and set the expression to sort by (by using alt-down-arrow, you can open a bigger editor with IntelliSense).
- make sure to set the ordering to descending

Please find a self-contained project attached with the sample data and analysis used in this example.

Regards,

Frank

 

[Björn Schaefer]

Hi Frank

you made my day... works perfectly.... many many thanks!

 

[chrisackland]

Grea

Hi Björn,

This is possible by using the sorting possibilities in the join collection.

I've got a simple example setup using excel files. In this case, there are two tables: main and sub.

View attachment 185

From these input collections, I've created two business collections: main and sub.

These two collections are the base collections in my analysis:

View attachment 186

To show the different possibilities in setting things up, I've created three different join collections.

1. The first collection is a simple join between the main and the sub-collection with an additional field taken from the sub.
View attachment 187

This shows the repeating behaviour that you've shown in your example.

2. The second collection is a join collection, but in this case, sorting the (joined) subs for each main and only picking the first one.
View attachment 188
Note that the ordering of the subs is done on the ID field and the ordering is ascending.
By indicating that only one record must be yielded of the sorted elements (for each main), this will only yield the first joined one.

3. The third collection uses the sorting, but in this case in descending order
View attachment 189

In this case, the ordering is set to descending. This means that the joined subs will be sorted in descending order (with respect to ID) and 1 element of this set is taken into account.

This translates to your case by:

- set the join to multiple elements
- switch the sorting and set the expression to sort by (by using alt-down-arrow, you can open a bigger editor with IntelliSense).
- make sure to set the ordering to descending

Please find a self-contained project attached with the sample data and analysis used in this example.

Regards,

Frank

Great explanation Frank!