One SKU has multiple mail date and trying to filter out all skus that have date greater than today

Utz
Utz Member
in SQL DataFlows

Hi, I am running into a problem with my visual I want to filter out any SKUs with a future mail date but since each SKU has multiple mail dates it is not recognizing the future ones it is going off the first mail date it sees. For example, the calculated field I have created is this "CASE WHEN MailDate > CURRENT_DATE() THEN 'Future Mail Date' ELSE 'Past or Present Mail Date' END" When I bring this field into my filter and filter on Past or Present Mail Date it will give me all SKUs that have a mail date in the past or present but if the SKUs also have mail date in the future it won't recognize and will bring them in as well that is what i want to remove. Only need SKUs with a past or present mail date with none in the future.

Tagged:

Best Answer

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hello @Utz,

    In ETL, group by the maximum 'MailDate' and join on SKU to obtain the latest 'MailDate'. Use your CASE statement on this result. Here's a similar example of this approach:

    image.png
    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • MichelleH
    MichelleH Coach

    @Utz Could you please share some screenshots of what you are seeing when you apply the filter?

  • Utz
    Utz Member

    Sure provided are the screenshots

    This is an example of the SKU that is giving me issues

    image.png

    Here are the mail dates associated with the SKU at hand 46476

    image.png

    my formula which is CASE WHEN MailDate > CURRENT_DATE() THEN 'Future Mail Date' ELSE 'Past or Present Mail Date' END is intended to filter out any SKU that has any occurrence of a future mail date when I select on "Past or Present" but this formula isn't working that way and SKU 46476 should not be showing up.

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hello @Utz,

    In ETL, group by the maximum 'MailDate' and join on SKU to obtain the latest 'MailDate'. Use your CASE statement on this result. Here's a similar example of this approach:

    image.png
    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories