Hello!
I have created two generic inquiries to be pulled into a main one. Numbers on the subqueries are correct, however the numbers are being pulled into the Main GI incorrectly. Any ideas are appreciated! I have tried changing the join type for some tables and changing aggregated functions but has had no luck.
The goal is for the GI to display amount applied to invoice, payment amount, amount applied to SO, $ value of shippable items, and $ value of non-shippable items, all grouped by SO.
Best answer by plambert
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.
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.
+8Nice explanation
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
