Automatically update the column value by current month and year in Magic ETL

Options
nica_tc
nica_tc Member
in Magic ETL

hi guys,

I got a table on below and some columns using month/year as column name.

image.png

i would like to make an automatically Magic ETL and set a logic condition like below:

if the date(column name) < current month and year (let say 08/2022)

then all value would be clear as 0 value under the column (01/2022, 02/2022, 03/2022......07/2022) and the value under 08/2022, 09/2022....12/2022 would not changed.

the goal is i dun want to change the data structure.

could someone give me some advice on it?

Tagged:

Answers

  • MichelleH
    MichelleH Coach
    Options

    Hi @nica_tc I don't believe there is a way to dynamically name columns in ETL, and having that many columns would likely be messy to manage anyway (one month's worth of data would result in 30 individual columns and you'd have to manually edit cards every time you want to add a day). I recommend keeping a separate date column and creating a pivot table card instead. The benefit of this is that it's also much easier to filter by timeframe and add dates.

  • nica_tc
    nica_tc Member
    Options

    hi @MichelleH,

    Thank you for the advice.

    in additional, if i keeping a separate date column and creating a pivot table card, for this pivot card is it possible to make as a input dataset for further processing?

  • MichelleH
    MichelleH Coach
    Options

    Hi @nica_tc you would not be able to use the card itself as a dataset input. You should be able to use the dataset powering the card as an input to another dataflow.

Categories