MYSQL Transform: Replacing date on historical input based on update of second input

jlazerus
jlazerus Member
in Magic ETL

I'm looking for the best way to do this. I have a SQL Transform with a recursive input and a second input which I'd like to have replace the input of the recursive data based on the date in one of the columns of the second input. The pictures below should help because I'm just as confused explaining this as you probably are reading it.

 

This image shows the historical data. Every day, the current date is appended to this dataset. Note the Date column (not batch date).

historical.png

 

This one shows the monthly update. Note the Date and Batch Date columns. Since this file gets finalized after the end of the month, we want this to replace what's in the historical dataset using the Date column.

monthly.png

 

Comments

  • AS
    AS Coach

    Can you just join the new with the old on the GL account code and in the output use the new date in place of the old date?

     

    Instead of 

    SELECT 

    old.`Date`, old.`GL Account`, old.`Gl Name`, old.`Balance`, old.`Batch Last Run`

    FROM

    historical as old

     

    Use something like:

    SELECT 

    new.`Date`, old.`GL Account`, old.`Gl Name`, old.`Balance`, old.`Batch Last Run`

    FROM

    historical as old left join monthly as new on old.`Date` = new.`Date`

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jlazerus
    jlazerus Member

    Would that replace the data in the `Balance` field for that date only? I still want to keep the rest of the data on the dataset.

Categories