COUNT(DISTINCT creates An issue has occurred during processing. We... Error

user02631
user02631 Member
in Magic ETL

I have created this first beastmode calc, and it seems to work just fine.

 

Case when `Total On Hand` - SUM(CASE when `Planned Shipment Date` < (CURDATE()+ 21) then `Outstanding Quantity` else 0 end) < 0 then `Item No` else 0 end

 

When I add a COUNT(DISTINCT to the end, it does not work and gives me the classic "An issue has occurred during processing. We..." error.

 

COUNT(DISTINCT Case when `Total On Hand` - SUM(CASE when `Planned Shipment Date` < (CURDATE()+ 21) then `Outstanding Quantity` else 0 end) < 0 then `Item No` else 0 end)

 

I would have expected it to work. Anybody have any suggestions?

 

Thanks

Best Answer

  • user02631
    user02631 Member
    Answer ✓

    Learned MySQL and used that instead of trying to do it in BeastMode. Got the results I needed.

     

    Thanks,

Answers

  • Godiepi
    Godiepi Coach

    Try This one

     

    COUNT(DISTINCT 
    Case when Sum(`Total On Hand`) - SUM(CASE when 
    `Planned Shipment Date` < (CURDATE()+ 21)
     then `Outstanding Quantity` else 0 end) < 0 then `Item No`  end)
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • ST_-Superman-_
    ST_-Superman-_ Coach

    try dropping the else 0 off of the end of the case statement

     

    (edit) @Godiepi apparently I should have refreshed my browser before posting my suggestion


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

    I copied that new statement in and it still gives me the "An issue has occurred during processing" error.

  • Godiepi
    Godiepi Coach

    There might be some Nulls that are causing the calculation to break so try adjusting your calculation for those Nulls like

     

    COUNT(DISTINCT 
    Case when Sum(`Total On Hand`) - SUM(CASE when 
    `Planned Shipment Date` < (CURDATE()+ 21)
     then ifnull(`Outstanding Quantity`,0)  else 0 end) < 0 then `Item No`  end)
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • ST_-Superman-_
    ST_-Superman-_ Coach

    Is this beastmode being used for a summary number?  Mind sharing the rest of the chart settings?


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

    Not sure what you mean by summary, but my guess is that the answer is yes.

     

    A little background. There are a list of items. Each item is assigned to a group.

     

    If you look at the first picture attached, you can see that some items have a negative value in the Try 1 column. What I would like to do is get the number of items with a negative value to appear next to the Count of Parts column in the second picture. Once that is accomplished, we can see how many items are in each group and then how many have a negative value based on the criteria from the earlier posts.

     

    As shown earlier, when I attempt to count the number of negative items via Count(Distincts and Case whens, it errors out.

     

    Let me know what other info you might need.

     

    Thanks for all of the help so far. Dojo 1.PNGDojo 2.PNG

  • Godiepi
    Godiepi Coach

    @user02631 

     

    oh that looks a little different from the prior description of what you are trying to do, but focusing for now in the second picture you can have a table card with 3 columns:

     

    1st column = Group

    2nd column = Beastmode, lets call it "Count of Negatives"

          code would be as simple as 

    ifnull(Count( Case when `Try 1` < 0 then `Try 1` end),0)

         The reason why I am not doing a Count Distinct here is that if there are 2 or more negatives with the same value it would counted once

    3rd column  =  `Try 1` , setting the aggregation to Count and naming the field "Count of Parts"

     

    @ST_-Superman-_  any ideas ?

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • ST_-Superman-_
    ST_-Superman-_ Coach

    @user02631 I think this would be easier if we could get a sample of the data.  Would you be able to share a sample of your dataset?  Ideally, it would need to contain 2 or 3 groups with several of the items from each group.  Feel free to anonymize the names of the items or groups, but without understanding the dataset structure, I think it will be very unlikely that we will get you the correct solution.


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

    Learned MySQL and used that instead of trying to do it in BeastMode. Got the results I needed.

     

    Thanks,

Categories