Income Statement: How do I join 'Chart of Account' table & 'GL Records' correctly
I have 2 tables:
- Chart of Account - Right Table: notice that there are Addon Repairs, Restocking Charges, Contractor Discount in 'Cost of Sales Others'
2. GL Records (for all branches from Feb 2024 - Sep 2024) - Left Table
Problem: I've tried both Full Join, Left Join (key: gl_account_id) then make a pivot table (filter a random Branch A, May 2024) and saw that Addon Repair, Restocking Charges, Contractor Discount are NOT in Cost of Sales Others in the Pivot Table.
And that's because in GL Records for Branch A, May 2024, there are no info for these 3 GL (as in those rows does not exist in the GL Records table)
My goal is to have those 3 GL still show in the Cost of Sales Others in the Pivot Table and the amount for each of these account = 0.00. But I'm not sure how I can do that
Best Answer
-
I see. So some branches and months may not exist and the join won't create rows for missing combinations. Sounds like you need a way to make sure the combinations have all relevant GL accounts, if if they don't exist in the GL Records.
You could handle this by creating a master dataset. Complete with all possible branch, month, and GL account combinations: Branch, Month, Year, GL Account. You could do this by joining a table containing all branches, a date table with years and months, and you chart of account table….which contains all the GL accounts.After you have a dataset with all possible combinations of branch, month, year, and GL account, perform a Left Join or Right Join (depending on how you set it up) with your actual GL Records table. If a record does not exist, the Amount will be NULL. Handle that as described previously.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **2
Answers
-
Perform a full outer join or a right join between the GL Records table and the Chart of Accounts table. The key for the join will be the gl_account_id. This will ensure that all rows from the Chart of Accounts table, including "Addons-Repairs," "Restocking Charges," and "Contractor Discounts," will remain in the result even if they don't have corresponding entries in the GL Records table.
After performing the join, the GL Records table will contain NULL values for the missing accounts (such as "Addons-Repairs") if they don't exist for a particular branch and period. You can use a conditional statement to replace those NULL values with 0.00 in the Amount column.
CASE
WHEN `Amount` IS NULL THEN 0.00
ELSE `Amount`
ENDor
COALESCE(`Amount`, 0.00)
This will replace any NULL values for Amount with 0.00, ensuring that those missing GL records (like Addons-Repairs, Restocking Charges, and Contractor Discounts) appear in the pivot table with an amount of 0.00.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose thank you for the response. However, problem with that is that in GL Records, some branches have "Addons-Repairs," "Restocking Charges," and "Contractor Discounts" and some may not. Same goes with Month, so some month will have those GL and some months do not.
So basically the join cannot distinguish which Branch and Month does not have those GL so it can leave those as null
Example: Branch A and B for Jan, Feb, Mar 2024 - Branch A, April does not have any info about Contractor Discounts, and so does Branch B, Jan Feb Mar also do not have any info on that.So if you Left Join in the Chart of Account table (Left), the join does not know which branch or which month that does not have Contractor Discount if that makes sense
0 -
I see. So some branches and months may not exist and the join won't create rows for missing combinations. Sounds like you need a way to make sure the combinations have all relevant GL accounts, if if they don't exist in the GL Records.
You could handle this by creating a master dataset. Complete with all possible branch, month, and GL account combinations: Branch, Month, Year, GL Account. You could do this by joining a table containing all branches, a date table with years and months, and you chart of account table….which contains all the GL accounts.After you have a dataset with all possible combinations of branch, month, year, and GL account, perform a Left Join or Right Join (depending on how you set it up) with your actual GL Records table. If a record does not exist, the Amount will be NULL. Handle that as described previously.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive