Income Statement: How do I join 'Chart of Account' table & 'GL Records' correctly

verytiredgirl · 2024-10-23T16:34:44+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Join

Magic ETL

Accepted answers

ArborRose

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.

All comments

ArborRose

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` 

END

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.

verytiredgirl

@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

ArborRose