How to exclude/filter out based on 2 dates

WorldWarHulk · 2023-07-26T13:06:52+00:00

There was an error rendering this rich post.

Hello Domo!

I uploaded a file/table in DOMO, that has a wipe date and a forecast date.

What I was hoping to do is, to use this table and join it in my dataset, so that I can use it to filter out everything within the wipe date and forecast date.

Is this possible?

I am having problems with joining it to my dataset.

Find more posts tagged with

Join

Magic ETL

Accepted answers

marcel_luthi

@WorldWarHulk keep in mind that you're basically doing a cross join for the tables, so for example if in the original table you had an entry with a date of 7/5/2021, this will appear once for each exclusion period you have in your other table, the one you shared a screenshot of, and the filter will only remove the entry for the period Jun 23 2023 - Jul 6, 2023, still leaving the others in.

If the idea is not to have repeated entries, but only remove from the original dataset those where the date field falls into any of the exclusion periods you have defined, you'll need to do a couple of extra steps after the cross join.

Add a Formula Tile that will identify if the date falls between the exclusion period:
CASE WHEN date >= Wipe Date (BOQ) AND date <= Forecast Due Date THEN 1 ELSE 0 END
Add a Group Tile that will include all the columns from your Original table and do either a SUM or MAX on the formula field we just created. This will return your dataset to it's original size, but now you'll have a flag that tells you whether that entry date falls under any of the given exclusion periods.
Now do the filter based on this rolled up flag being 0, so you are keeping only the entries that do not fall under any exclusion period.
Use an Output Tile to write it to a new dataset you'll use for your cards.

Hope this helps.

All comments

ColemenWilson

Yes, it is possible. What problems are you having when joining?

GrantSmith

In Magic ETL JOIN tile can't join using a between clause however you can add a single column to each dataset, give it a name of "Join Column" with a value of 1, then join both datasets together based on this field and then use a filter tile to filter our records were it's not between the two dates.

WorldWarHulk

Hi @GrantSmith

I was able to join the 2 dataset using the join column, I used the full outer to include all rows from both datasets.

I am trying to figure out now what to do with the filter tile.

Basically, I want to show everything except those that are within the Wipe Date and Forecast Due Date.

ColemenWilson

Drop this formula into your filter tile, be sure to select "Add formula rule" as the filter type:

`date` <= `wipe_date` AND `date` >= `forecast_due_date`

marcel_luthi

Add a Formula Tile that will identify if the date falls between the exclusion period:
CASE WHEN date >= Wipe Date (BOQ) AND date <= Forecast Due Date THEN 1 ELSE 0 END
Add a Group Tile that will include all the columns from your Original table and do either a SUM or MAX on the formula field we just created. This will return your dataset to it's original size, but now you'll have a flag that tells you whether that entry date falls under any of the given exclusion periods.
Now do the filter based on this rolled up flag being 0, so you are keeping only the entries that do not fall under any exclusion period.
Use an Output Tile to write it to a new dataset you'll use for your cards.

Hope this helps.

WorldWarHulk

Hi @marcel_luthi

You are correct. it did created multiple repeated entries.

I don't know if I am doing something wrong but, I am getting 4million rows compared to the original dataset which only has 255k rows. even after filtering based on 0.

marcel_luthi

You'd need to check your group by clause, make sure you're not including the Wipe Date (BOD) nor the Forecast Due Date as the section that Identifies the grouping.

Just after the group by the number of entries should go back to the same number as your original dataset.

WorldWarHulk

Thanks Marcel that did it!

Also, I used Sum instead of Maximum in the group by tile and I was able to get the results I wanted.

and thanks also to everyone who commented and gave inputs.