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

verytiredgirl
verytiredgirl Member
edited October 23 in Magic ETL

I have 2 tables:

  1. Chart of Account - Right Table: notice that there are Addon Repairs, Restocking Charges, Contractor Discount in 'Cost of Sales Others'
    image.png

2. GL Records (for all branches from Feb 2024 - Sep 2024) - Left Table

image.png

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)

image.png

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

Tagged:

Best Answer

  • ArborRose
    ArborRose Coach
    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! **

Answers

  • ArborRose
    ArborRose Coach

    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

    or

    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! **

  • verytiredgirl
    verytiredgirl Member
    edited October 23

    @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.

    image.png

    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
    ArborRose Coach
    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! **

Categories