Using two GIs as data source

Aggregating two separate inquiries in one GI without them interfering with each other is tricky business. Thankfully, there is a trick if you want to accomplish this directly, or you can restructure the other two GIs to have the same ‘Group By’ fields and handle the aggregation in those source GIs before you combine them.

The trick, frequently called the ‘Wyatt.ERP’ technique after the user who suggested it on this forum post, is to introduce a fixed-size full join to the query so as to create duplicate records with unique values to leverage that allow you to keep your unrelated GIs separate. I like to think of it as creating separate slots for each data source that may have multiple records so they don’t duplicate each other.

First you need a join to predictably duplicate every record that can be limited to a controlled number of duplicates. Some people have set up an attribute with values 1,2,3… , though I prefer to use PX.SM.DateInfo, which has extended information about every date starting Jan 1st, 1970, with one record per date. Credit to lauraj46 for the idea. It looks something like this:

After this join, each SOOrder that gets included will be duplicated exactly twice, once with a DateInfo.DateInt of 19700101, and the other with 19700102.

After that, include a condition on the joins of other tables or GIs to sort them into either the ...01 or ...02 slots.

At the end of all your joins, the ungrouped query will have a structure like this:

OrderNumber             DateInt          SOLineNumber       SOAdjustID

000001           19700101               1                             -

000001           19700101               2                             -

000001           19700101               3                             -

000001           19700102               -                             1

000001           19700102               -                             2

Finally, group by OrderNumber. Your aggregations for SOLine data and SOAdjust (payment) data will then not have interfered with each other.

Nice explanation ​@plambert !