I need to validate 2 finance datasets to ensure the numbers match for all GL accounts.
Dataset A:
Columns: Account_name, Branch_id, MTD, MTD Budget, MTD Last Year, YTD, YTD Budget, YTD Last Year.
This dataset is generated monthly from the ERP system, with pre-calculated values. I need to append it each month after adding year and month columns in Excel.
Dataset B:
Columns: Year, Month, Branch_id, GL_account_id, Account_name, Balance_Amount, Budget_Amount.
This dataset is a replica from an on-prem DB and does not include MTD, MTD Budget, etc. calculations. Data ranges from Feb 2023 to Sep 2024.
To validate, I want to join these datasets using the keys: account_name, year, month, and branch_id. Since I can’t convert A to B due to missing individual monthly breakdowns for YTD, I prefer converting B to A.
I was able to turn Dataset B → A using BeastMode to calculate MTD, MTD Budget, MTD Last Year, YTD, YTD Budget, YTD Last Yea values, but I can’t join it with Dataset A for validation. Can I convert B to A using Magic ETL & then join in dataset A?
My concerns with ETL is since I have multiple month, I'm not sure how to write SUM(Case when) so the all the MTD, YTD, calculations for every month. Fiscal year start from Feb to Jan next year, I could add a month end date column so it's easier for case when
