Beast Mode for same date check

helpme12345
helpme12345 Member
in Beast Mode

I have a list of companies and their create date. I want to create a beast mode that checks if a list of companies has the same create date. Any help here?

Would this need to be done in Magic ETL?

Tagged:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited January 22 Answer ✓

    Hmm the way I would do this is still in MagicETL. I would convert the timestamp using a formula tile with the following formula: 

    CONCAT(DATE_FORMAT(`Date`,'%m/%d/%y %k:'),(CEILING(DATE_FORMAT(`date`,'%i') / 5) * 5))

    Then follow the same process I mentioned earlier, but use this field instead of your date field. What this will do is group everything to the nearest 5 minutes. So if you have a company created at 12/01/2023 8:15AM and another at 12/01/2023 8:13AM they will be grouped together.

    If I solved your problem, please select "yes" above

Answers

  • nmizzell
    nmizzell Contributor
    edited January 22

    You can create a pivot table with the rows field as created date, and a second rows field as the company name. This will give you all companies by creation date. You will need to put a dummy value into the values box so that the chart will render. you can just do a count of rows, it does not matter since its a dummy column.

  • helpme12345
    helpme12345 Member

    Let me rephrase. I want to create a beast mode filter that will allow me to see only companies that are created minutes apart

  • ColemenWilson
    ColemenWilson Coach
    edited January 22

    Using MagicETL you could use the group by tile and choose your create date as the column that identifies the grouping. Then select "Add Formula" and use GROUP_CONCAT(`Company`). The output will be a comma separated list of companies for each create date.

    It would be helpful if you could share what kind of output you are looking for and how it will be used.

    If I solved your problem, please select "yes" above

  • helpme12345
    helpme12345 Member

    @ColemenWilson Is there a way to do this in Beastmode? For example, I have a list of 20 companies and the create date (is a timestamp) per day, and want to discern which have been created around the same time

  • ColemenWilson
    ColemenWilson Coach
    edited January 22 Answer ✓

    Hmm the way I would do this is still in MagicETL. I would convert the timestamp using a formula tile with the following formula: 

    CONCAT(DATE_FORMAT(`Date`,'%m/%d/%y %k:'),(CEILING(DATE_FORMAT(`date`,'%i') / 5) * 5))

    Then follow the same process I mentioned earlier, but use this field instead of your date field. What this will do is group everything to the nearest 5 minutes. So if you have a company created at 12/01/2023 8:15AM and another at 12/01/2023 8:13AM they will be grouped together.

    If I solved your problem, please select "yes" above

  • helpme12345
    helpme12345 Member

    I'll try this out! Thanks so much @ColemenWilson

Categories