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

ozarkram

Hi All,

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

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

Reference table:

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

MarkSnodgrass

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

....

ozarkram

@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

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.

ozarkram

Sure @MarkSnodgrass ..Appreciate your suggestion!