Fire event - if input has no rows

Vera O
Vera O Member
edited June 3 in General

Hi All,

I would like to fire an event (run a specific workflow) if NO rows in the input - but there is only an option to fire events if input has rows:

image.png

Basically the goal is to only send correct data to an external system (Snowflake) if there are no errors (at all) in a dataset.

Is there a way to set this?

Many thanks!

Kind regards,

Vera

Answers

  • Josh Boxer
    Josh Boxer Administrator

    Hi Vera

    There are lots of ways to solve for this. One idea is as follows:

    image.png
    1. add a column with any constant value (for all rows) that can be used in a Lookup
    2. Lookup the lowest row score across the entire column (for all rows)
    3. Filter less than 100 on the lookup column, i.e. if any row is less than 100 then treat them all as below 100
    4. Fire event if there are rows, which will mean the lowest score of all rows is 100 or there will be no rows
  • Vera O
    Vera O Member
    edited June 4

    Hi Josh,

    could you please send me a screenshot of the lookup definition part? Not sure I understand that part.

    I also created an over-complicated version 😅 but it seems to work:

    image.png

    Basically I'm checking if there are failing and passing rows as well and if there are only passing rows then it has a row buy the end, if not, there are no rows:

    image.png

    By the way it would be a good improvement idea to have a Fire event trigger if no rows in the input 😉

    Thanks!

    Kind regards,

    Vera

  • Henry Simms
    Henry Simms Administrator

    I've had to solve this problem too. My approach is typically something like this:

    image.png

    Profiling on zero rows of data results in this output:

    image.png

    And you can then use the following very simple filter in the Split step:

    image.png

    There's other approaches you might take too. If you're dealing with potentially large numbers of rows, you might want to Sample (with row limit 1) before the Profile to make sure you're not wasting processing time on unnecessary profiling, if you don't need to know exactly how many rows there are, only that it's more than "0" or ">0".

    As a bonus, you could pass also pass the row count into the events as a parameter, and then use it in the notification.

    image.png
  • Vera O
    Vera O Member

    Hi Henry,

    You gave me a good idea! I’ll simply check if the failing rows are 0, and if so, fire the event:

    image.png

    That's the most simple way

    Thanks!

    Vera