How to exclude/filter out based on 2 dates

WorldWarHulk
WorldWarHulk Member
in Magic ETL

Hello Domo!

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

image.png


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.

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    @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.

    1. 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
    2. 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.
    3. 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.
    4. Use an Output Tile to write it to a new dataset you'll use for your cards.

    Hope this helps.

Answers

  • ColemenWilson
    ColemenWilson Coach

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

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

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • WorldWarHulk
    WorldWarHulk Member

    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.

    image.png

  • ColemenWilson
    ColemenWilson Coach
    edited July 2023

    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`

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

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    @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.

    1. 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
    2. 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.
    3. 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.
    4. Use an Output Tile to write it to a new dataset you'll use for your cards.

    Hope this helps.

  • WorldWarHulk
    WorldWarHulk Member

    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.

    image.png

  • marcel_luthi
    marcel_luthi Coach

    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.

    image.png

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

  • WorldWarHulk
    WorldWarHulk Member

    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.

Categories