topic Re: Aggregate Outer Join data into 1 row only in App Development

Aggregate Outer Join data into 1 row only

orlando162 — Fri, 24 Jun 2022 10:06:57 GMT

Dear all,

I'm going crazy with joins:

I would like to aggregate joined dimensions into a single row.

I used [ID_Invoice] as common dimension to create outer join. It should be so easy for you, sorry I'm pretty nearbie about these things.

Re: Aggregate Outer Join data into 1 row only

marcus_sommer — Fri, 24 Jun 2022 12:44:45 GMT

There is a missing/wrong association in regard to the field/dimension "Project" and/or a data-quality issue.

You might just remove this field from your table to get a single row. Otherwise if this information is important for this view you will need to look on your data and/or adjusting the data-model.

- Marcus

Re: Aggregate Outer Join data into 1 row only

orlando162 — Fri, 24 Jun 2022 13:36:08 GMT

The association is made with the field [ID_Invoice]:

[Sirio]:

LOAD
[Mandante] as [Company],
[Data] as [Data_Registrazione],
[Data] as Master_Data,
[Importo (CHF)] as [Importo_CHF],
[Importo (Valuta)]&'_'&[Numero documento] as [ID_Invoice]

OUTER JOIN ([Sirio]) LOAD
[Project],
[# Invoice],
[Amount],
[Amount]&'_'&[# Invoice] as [ID_Invoice]

Re: Aggregate Outer Join data into 1 row only

marcus_sommer — Fri, 24 Jun 2022 13:58:30 GMT

The effect which you see may not only be caused from this join else also from other script-parts and/or of the fields which are used within this table.

Further it looked as if you are combining a measure-field and an ID-field to a key-field. If so it's quite unusual and I suggest to check if this approach is really expedient.

Beside this I suggest to add definite unique id's within the loads, for example with recno() and rowno() and then using a table-box with all relevant fields to see which associations are really there and how the data-quality looked like.

- Marcus