Validating datasets that has different formats

verytiredgirl

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

ArborRose

If your fiscal year starts in February, you need some type of fiscal year definition, Fiscal Year.

CASE 

    WHEN MONTH >= 2 THEN YEAR 

    ELSE YEAR - 1 

END

For a date calculation, you would have something like

CONCAT(Year, '-', Month, '-01')

To get the last day of the month, use:

1. Add Days function: Add 1 month to the first day of the month.
2. Add Days again: Subtract 1 day to get the last day of the current month.

ArborRose

If your fiscal year starts in February, you need some type of fiscal year definition, Fiscal Year.

CASE 

    WHEN MONTH >= 2 THEN YEAR 

    ELSE YEAR - 1 

END

For a date calculation, you would have something like

CONCAT(Year, '-', Month, '-01')

To get the last day of the month, use:

1. Add Days function: Add 1 month to the first day of the month.
2. Add Days again: Subtract 1 day to get the last day of the current month.