Get First of Month in ETL

user02369
user02369
in Archive

I'm creating an ETL flow that aggregates data by month.  Is there a way to roll up my date field to just the first day of that month?  I can extract the Month and the Year separately, but I need a result field that combines them back into a single 'First Day of the Month' field.

I.e. RegistrationDate = July 3 2018, First Day of the Month = July 1 2018

 

Right now the closest i can get is RegistrationMonth = 7, RegistrationYear = 2018.  How do i put those back together to get July 1 2018 (or 7/1/2018 works too)?  It needs to be done in the ETL process, not in a Beast Mode on the card because I want the aggregated value to be a distinct count and if I aggregate to the date level in ETL, it could result in duplicate counts when i aggregate again in the card to the month level.

 

Thanks

Comments

  • Valiant
    Valiant Coach

    If you've got your Month and Year already, you could do the following:

    1. Add a constant where the value is 1 and the type is text

    2. Add a Combine Columns and follow the screenshot below to combine the Month, 1, and Year into a string your example + mine would result in a string of 7/1/2018

    3. Use Set Column Type with 'Date' and 'Month First' to convert it to a date type.

    image.png

     

    The steps and flow order above should give you a new Date column where all dates are the first of the month.

     

    Let me know if you have any questions,

    Valiant_Ronin

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • KVincent
    KVincent

    I know you were looking for the first day ability, but that does not appear to be an option. If last day would be an option then I could help, but it will involve a MYSQL dataflow. Here is the syntax that you would use.

    Select last_day(Date) as 'Month End Date'

    This will create a new column that returns the last day of the month from the date column you are working in.

  • rado98
    rado98 Contributor

    Two ways come to mind

     

    1

    Create a in excel a dataset that has two columns "Date" and the matching "First day of the month date".

    Join that to your current dataset. Not elegant but it will work.

    I have a fiscal calendar dataset that has this plus last day of month, quarter, year, half etc.

     

    2 

    In magic ETL

    Convert the date to decimal

    Multiply "Day of Month" by 1000*60*60*24 (converts from day to miliseconds)

    Subtract that from Date as decimal

    Convert back to Date

Categories