Adding an "Activity_Date" to a nightly refresh dataset.

Golfoholic
Golfoholic Member
in Magic ETL

I just set up a connection for a dataset that doesn't have any fixed 'date' column. The only date column it will have will be the batch_last_run date. On a nightly basis this dataset will append and start to build history. How can I add a new date column in a format such as 'Jul 1, 2021', so that the new data and old data have a correlating date?

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user048760 I use that same connector with the append and I use the batch_last_run to identify the groups and when they were added. There are a variety of date format options you can use in the Analyzer and in Magic ET if you want to get rid of the time portion of the batch last run column. In Analyzer, when you add the field to the chart, you can choose Format - Date Shorthand.

    You can also create a beast mode and use the DATE() function to extract the date portion of the field.

    In Magic ETL, you can set the column type to Date.

    **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.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user048760

    You'd need to utilize a date tile or formula tile to assign the current timestamp in a Magic ETL DataFlow.

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

    @GrantSmith wouldn't that update all rows with the current timestamp rather than just the latest rows that were appended? It seems like you would need to utilize a recursive dataflow to apply the current date to the new rows that were appended.

    @user048760 Which connector are using to bring your data in? There are a couple connector types that allow you to add a timestamp date on the new data on import, but I am not remembering which right now.

    **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.
  • Golfoholic
    Golfoholic Member

    @MarkSnodgrass The data is coming in through an email connector in an xlsx format.

  • GrantSmith
    GrantSmith Coach

    @user048760

    What exactly are you wanting this date to represent? As you said you already have the batch last run date to show when that group of data was imported. Are you just wanting to reformat the date?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user048760 I use that same connector with the append and I use the batch_last_run to identify the groups and when they were added. There are a variety of date format options you can use in the Analyzer and in Magic ET if you want to get rid of the time portion of the batch last run column. In Analyzer, when you add the field to the chart, you can choose Format - Date Shorthand.

    You can also create a beast mode and use the DATE() function to extract the date portion of the field.

    In Magic ETL, you can set the column type to Date.

    **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.
  • Golfoholic
    Golfoholic Member

    @GrantSmith Yes, essentially have a date column in a different format. Batch last run, along with a Month Day Year format.


    @MarkSnodgrass By doing this in ETL, would I have to pull in the Date operations tile to set the column type or is that something I could just go in, click something on the column and select what column type?

  • MarkSnodgrass
    MarkSnodgrass Coach

    @user048760 If you have the Magic ETL 2.0 beta, you can set as Date when you click on the input dataset configuration. If you have the regular Magic ETL, you would add the Set Column Type tile and select your column and tell it to use the type of Date.

    **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.
  • GrantSmith
    GrantSmith Coach

    @user048760

    If you just want to display your date in a different format you can utilize the DATE_FORMAT function within a beast mode:

    DATE_FORMAT(`dt`, '%b %d, %Y')

    This way you have a single date column in your dataset and don't need to worry about maintaining both throughout your ETL process.

    See https://www.w3schools.com/sql/func_mysql_date_format.asp for more information on the formatting codes

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

Categories