ETL - Create Column with Max Date

dtysick

I am creating a dashboard using the Illustrator plugin so Beast Mode calculations will not work unfortunately. I am looking to show the date of the newest data on the dashboard. I have a column called "Date" so I need to pull the max date and stamp it to a new column.

 

Is that possible or will I have to use a SQL dataflow?

 

Thanks!

Unknown

Hi all,

Can anybody help out @dtysick?

Thanks!

user0010285

I'm also looking to do this in Magic ETL. Any resolution?

Shevy

You will need to add it as a column to your data set - you can do this in Magic ETL or in your original data set update.

 

First, if you can change the data set you are bringing in to add a "Current Date" or a "Max Date"in our SQL statement, that is probably the easiest way.  IF you do not have access to change the code that creates the data set then Magic ETL is the way to do it.

 

The ETL looks like this:

 

 

If you have a column in your table that is constant for all entries, you can kip the Add Constants part.  For Add Constants I add a column I call "Constant" and put in the value of 1 (this could be anything you want).  

Now group data by the constant and the new field is "Latest Date" and select the column the Date is in and select Maximum in the aggregation method field.  something like this:

 

 

Now combine the group by and the Constant boxes with a join and the "Constant Field" is the common link.  Have all fields go to the new outfile and then you will have an outfile to use in your cards / illustrator.

 

 

 

 

 

nitot

This is an excelent solution, thanks for posting!