ETL or beast mode for Dynamic assignment of month based on current date

ozarkram
ozarkram Member
in Magic ETL

Hi All,

Hope you are doing well..I am trying to convert a plan table as below

image.png

into the table below based on the current date and the reference table

image.png

Reference table:

image.png

So I need to basically map the column names (FC1,FC2,FC3...input table) to fiscal month based on the current date and looking up the reference table for the fiscal month... I have attached the excel data for the above too...Can you please help me with the beast mode or ETL for the same...

Sample Data Sheet.xlsx


Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    It seems like your columns names are locked into what they should be renamed to regardless of what the current date is. Wouldn't you just use the Select Columns tile in Magic ETL and change the columns there?

    FC1 -> Apr-22

    FC2 -> May-22

    FC3 -> Jun-22

    ....

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ozarkram
    ozarkram Member

    @MarkSnodgrass : The column names should change every fiscal month according to the reference table dynamically ..For example FC1 should be renamed to May 2022 and FC2 should be renamed to June 2022 from 24th April 2022...Similarly from 22nd may 2022 FC1 should be renamed to June 2022, FC2 should be renamed to July 2022...

  • MarkSnodgrass
    MarkSnodgrass Coach

    Well.. I suppose you could do something in MySQL by using the information_schema table during the process. You could google mysql dynamic column names and see if that is what you would want to do. However, having new column names all the time will require you to update any cards tied to that dataset. The cards won't be dynamic as far as automatically adding new columns to your card.

    Just something to consider.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ozarkram
    ozarkram Member

    Sure @MarkSnodgrass ..Appreciate your suggestion!

Categories