Bucketing

user056243
user056243 Member
in Magic ETL

I am trying to get bucketing for a certain data

the bucket is

250+ employees  (count of female)  (count of male) (their total salaries)

300+ employees  (count of female)  (count of male) (their total salaries)

350+ employees  (count of female)  (count of male) (their total salaries)

But when i try in beast mode 

CASE WHEN `employee` > 250000
then '250k'
WHEN `employee` > 300000
then '300k'
WHEN `employee` > 350000
then '350k'
ELSE 'OTHER'
END

it gives the only 250k

 

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user056243 

     

    This is because the order in which your conditions are being evaluated. If you had 400,000 it compares to the first expression (>250) which is true so it returns '250k'. When doing a beast mode like this you need to start largest and go in descending order.

     

    CASE WHEN `employee` >= 350000
then '350k'
WHEN `employee` >= 300000
then '300k'
WHEN `employee` >= 250000
then '250k'
ELSE 'OTHER'
END

     

    I also updated your logic to be >= instead of > so that if you happened to have exactly 250,000 it'd fall in the correct bucket.

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

    When I do as you suggested

    350k shows 200

    300k shows 110

    250k shows 95

     

    Ideally 250k should shows 95+110+200

    300k should show 110+200 

     

  • GrantSmith
    GrantSmith Coach

    A single beast mode won't work for how you're wanting to process your data. You're wanting to include a single record into multiple buckets which isn't possible as a single value is returned for each row from a beast mode. You could try and create separate beast modes for each bucket.

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

Categories