Min(Date) function in Magic ETL to create a "Date of First Sale"?

Our client wants us to create a dataflow in Magic that includes a "date of first sale" column. In Redshift, we're able to perform a min("1st sale") calculation, but we're not able to find a way to perform this function within Magic ETL. The client has expressed that being able to use Magic is critical to them, so we really need to figure out a solution. Any ideas would be greatly appreciated.

Comments

  • You can do that in beastmode or on, for example, a table card; why Magic?  That sort of aggregation work is frequently done on the card level, unless it's a step in a longer process.

  • @mhutch84, did the above reply help you out?

  • Atanas
    Atanas Sydney, Australia

    domo

    💎

    You can do it using the "Group By" tile. You will need to group over the whole dataset, i.e. the Group By column will need to be something like the _BATCH_ID, which is usually the same across the full source dataset.

     

    The function then of the Group By tile is Minimum, and you applying that function over the column from whick you would like to extact the minimum, e.g. Date.

     

    See the below screenshots. Hope this helps!

     

    Screen Shot 2019-07-11 at 3.43.17 PM.pngScreen Shot 2019-07-11 at 3.43.59 PM.png