Forum Discussion
Linking Tables
Hi, I have two tables that i am trying to link using common identifier and bring the corresponding values from one table to another. When i import the table and link with the common identifier (P...
Thank you Ashish.
There are two tables i am trying to link to. One is called "Smmary", second one is called Gross Sales. Output table will have Gross Sles right next to Franchise OIS where Calendar Year is 5/31/2023. I linked Product Hierarchy and Calendar year in both tables and setup many to one relationship. It doesnt allow many to many relationship.
After linking tables it shows wrong numbers for Gross Sales.
Hope this clarifies.
Output Table
| Calendar Year | Product Hierarchy | Invoice Sales | Gross Sales |
| 5/31/2023 | Sump Pump 1 | -2 | $8,948 |
| 5/31/2023 | Sump Pump 2 | 56,324 | $5,082 |
| 5/31/2023 | Sump Pump 3 | 9,731 | $10,908 |
| 5/31/2023 | Sump Pump 4 | 6,230 | $85,625 |
| 5/31/2023 | Sump Pump 5 | 0 | $158,391 |
| 5/31/2023 | Sump Pump 6 | 0 | $113,247 |
| 5/31/2023 | Sump Pump 7 | 24 | $7,466 |
| 5/31/2023 | Sump Pump 8 | 0 | $99,211 |
| 5/31/2023 | Sump Pump 9 | 0 | $89,401 |
| 5/31/2023 | Sump Pump 10 | 672 | $1,214,519 |
| 5/31/2023 | Sump Pump 11 | 532 | $93,926 |
| 5/31/2023 | Sump Pump 12 | 0 | $31,913 |
| 5/31/2023 | Sump Pump 13 | 0 | $180,980 |
| 5/31/2023 | Sump Pump 14 | 157,571 | $69,384 |
| 5/31/2023 | Sump Pump 15 | 4,833 | $161 |
| 5/31/2023 | Sump Pump 16 | 0 | $85,898 |
| 5/31/2023 | Sump Pump 17 | 2,848 | $279,286 |
| 5/31/2023 | Sump Pump 18 | 25,425 | $12,516 |
| 5/31/2023 | Sump Pump 19 | 4,606 | ($839) |
Summary
| Calendar Year | P&L | Product Hierarchy | Values |
| 5/31/2022 | Units | Sump Pump 19 | 1,161 |
| 5/31/2022 | Units | Sump Pump 20 | 478,981 |
| 5/31/2023 | Invoice Sales | Sump Pump 1 | $8,948 |
| 5/31/2023 | Invoice Sales | Sump Pump 2 | $5,082 |
| 5/31/2023 | Invoice Sales | Sump Pump 3 | $10,908 |
| 5/31/2023 | Invoice Sales | Sump Pump 4 | $85,625 |
| 5/31/2023 | Invoice Sales | Sump Pump 5 | $158,391 |
| 5/31/2023 | Invoice Sales | Sump Pump 6 | $113,247 |
| 5/31/2023 | Invoice Sales | Sump Pump 7 | $7,466 |
| 5/31/2023 | Invoice Sales | Sump Pump 8 | $99,211 |
| 5/31/2023 | Invoice Sales | Sump Pump 9 | $89,401 |
| 5/31/2023 | Invoice Sales | Sump Pump 10 | $1,214,519 |
| 5/31/2023 | Invoice Sales | Sump Pump 11 | $93,926 |
| 5/31/2023 | Invoice Sales | Sump Pump 12 | $31,913 |
| 5/31/2023 | Invoice Sales | Sump Pump 13 | $180,980 |
| 5/31/2023 | Invoice Sales | Sump Pump 14 | $69,384 |
| 5/31/2023 | Invoice Sales | Sump Pump 15 | $161 |
| 5/31/2023 | Invoice Sales | Sump Pump 16 | $85,898 |
| 5/31/2023 | Invoice Sales | Sump Pump 17 | $279,286 |
| 5/31/2023 | Invoice Sales | Sump Pump 18 | $12,516 |
| 5/31/2023 | Invoice Sales | Sump Pump 19 | ($839) |
| 5/31/2023 | Invoice Sales | Sump Pump 20 | $476,981 |
Gross Sales
| Calendar Year | Product Hierarchy | Gross Sales | Segment | Values |
| 5/31/2023 | Sump Pump 1 | Gross Sales | High End | $18,948 |
| 5/31/2023 | Sump Pump 2 | Gross Sales | High End | $15,082 |
| 5/31/2023 | Sump Pump 3 | Gross Sales | High End | $20,908 |
| 5/31/2023 | Sump Pump 4 | Gross Sales | High End | $95,625 |
| 5/31/2023 | Sump Pump 5 | Gross Sales | High End | $168,391 |
| 5/31/2023 | Sump Pump 6 | Gross Sales | High End | $123,247 |
| 5/31/2023 | Sump Pump 7 | Gross Sales | High End | $17,466 |
| 5/31/2023 | Sump Pump 8 | Gross Sales | High End | $109,211 |
| 5/31/2023 | Sump Pump 9 | Gross Sales | High End | $99,401 |
| 5/31/2023 | Sump Pump 10 | Gross Sales | Low End | $1,224,519 |
| 5/31/2023 | Sump Pump 11 | Gross Sales | Low End | $103,926 |
| 5/31/2023 | Sump Pump 12 | Gross Sales | Low End | $41,913 |
| 5/31/2023 | Sump Pump 13 | Gross Sales | Low End | $190,980 |
| 5/31/2023 | Sump Pump 14 | Gross Sales | Low End | $79,384 |
| 5/31/2023 | Sump Pump 15 | Gross Sales | Low End | $10,161 |
| 5/31/2023 | Sump Pump 16 | Gross Sales | Low End | $95,898 |
| 5/31/2023 | Sump Pump 17 | Gross Sales | Low End | $289,286 |
| 5/31/2023 | Sump Pump 18 | Gross Sales | Low End | $22,516 |
| 5/31/2023 | Sump Pump 19 | Gross Sales | Low End | $9,161 |
| 5/31/2023 | Sump Pump 20 | Gross Sales | Low End | $486,981 |
Ashish_Mathur
Super User
Super UserHi,
Please find attached the PBI file.
Hope this helps.