Validating datasets that has different formats

verytiredgirl
verytiredgirl Member
edited November 18 in Magic ETL

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

Tagged:

Answers

  • ArborRose
    ArborRose Coach

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories