Beast Mode - Checking data from a different row to generate result

user02324
user02324 Member
edited April 2023 in Beast Mode

I'd like to create a Beast Mode that takes a value and checks it against another row in my table.

I have a bunch of human annotation data. I want to look at a content_id and then flag if that content_id has been in the QC job. I've made a rough sketch of how the data looks (apologies for the formatting. This is my first time using this message board)

Basically I want to create a Beast Mode that generates the 3rd column (QC Status) that returns a TRUE if any row in the dataset has a content_id that matches AND has been in the QC job.

content_id | job_name | QC Status

001 | original_job | TRUE

002 | original_job | FALSE

001 | QC_ob | TRUE

Ive done some basic case when beast modes in the past, but I cannot figure out how to aggregate this.

Best Answers

  • MarkSnodgrass
    Answer ✓

    You would need to do this in Magic ETL. You can use the filter tile to filter where job_name equals QC_Job and then use the join tile to join back to your main dataset and do a left join on content_id with your main dataset being on the left side of the join. The left join will allow all of your main dataset rows to come through while showing a match with the qc_jobs, which you can then label as true.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ST_-Superman-_
    edited April 2023 Answer ✓

    You could try a fixed function. Something like this might work:


    Case

    When sum(count(‘ContentID’)) fixed (by ‘ContentID’) > 1

    AND sum(sum(case when ‘JobName’=‘QC_Job’ then 1 else 0 end)) fixed ()

    THEN ‘True’ else ‘False’ end



    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • MarkSnodgrass
    Answer ✓

    You would need to do this in Magic ETL. You can use the filter tile to filter where job_name equals QC_Job and then use the join tile to join back to your main dataset and do a left join on content_id with your main dataset being on the left side of the join. The left join will allow all of your main dataset rows to come through while showing a match with the qc_jobs, which you can then label as true.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ST_-Superman-_
    edited April 2023 Answer ✓

    You could try a fixed function. Something like this might work:


    Case

    When sum(count(‘ContentID’)) fixed (by ‘ContentID’) > 1

    AND sum(sum(case when ‘JobName’=‘QC_Job’ then 1 else 0 end)) fixed ()

    THEN ‘True’ else ‘False’ end



    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Just realized that I never finished my case statement here:

    Case
    
    When sum(count(‘ContentID’)) fixed (by ‘ContentID’) > 1
    
    AND sum(sum(case when ‘JobName’=‘QC_Job’ then 1 else 0 end)) fixed () >=1
    
    THEN ‘True’ else ‘False’ end
    


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman