Beast Mode for same date check

renee12345

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?

ColemenWilson

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.

nmizzell

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.

renee12345

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

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.

renee12345

@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

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.

renee12345

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