Adding an "Activity_Date" to a nightly refresh dataset.

Golfoholic

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?

MarkSnodgrass

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

GrantSmith

Hi @user048760

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

MarkSnodgrass

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

Golfoholic

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

GrantSmith

@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?

MarkSnodgrass

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

Golfoholic

@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

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

GrantSmith

@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