Beast Mode Turning numbers into a Letter Grade

user03552
user03552 Member
in Magic ETL

Hey everyone, 

 

I am currently working on a project where I have built a data set to see the turn around times on our containers coming in to our warehouses. I am trying to make it so that it will assign each warehouse a letter grade depending on how many days it takes them to empty out a shipping container. I have been trying to write a statement so that it will work but I keep getting a syntax error. Im basically trying to change a range of numbers into letter grades just like they would in high school.

 

Here are the different statements I have written. None of which have worked thus far. I don't paticularly care which one I use I am just trying to get it to work. 

 

Thanks for your help!

 

Attempt 1:

(CASE

when `actual_ship_date`-`release_date` >=2 then "A"

when `actual_ship_date`-`release_date`between 2.001 and 3.00 then "B"

when `actual_ship_date`-`release_date`between 3.001 and 4.00 then "C"

When `actual_ship_date`-`release_date`between 4.001 and 5.00 then "D"

when `actual_ship_date`-`release_date`> 5.00 then "F"

else "null")

 end

 

Attempt 2:

if (`actual_ship_date`-`release_date` >=2)

grade = "A";

else if (`actual_ship_date`-`release_date` >= 2.01 && `actual_ship_date`-`release_date` <= 3)

Grade = "B";

else if (`actual_ship_date`-`release_date` >= 3.01 && `actual_ship_date`-`release_date` <= 4)

Grade = "C";

else if (`actual_ship_date`-`release_date` >= 4.01 && `actual_ship_date`-`release_date` <= 5)

Grade = "D";

else if (`actual_ship_date`-`release_date` >= 5.01)

Grade = "F";

End

 

Attempt 3:

(CASE

 when `actual_ship_date`-`release_date`< 2 then "A"

 when `actual_ship_date`-`release_date`< 3 then "B"

 When `actual_ship_date`-`release_date`< 4 then "C"

 When `actual_ship_date`-`release_date`< 5 then "D"

 When `actual_ship_date`-`release_date`>= 5 then "F"

 Else "Null")

Best Answer

  • AS
    AS Coach
    Answer ✓

    It's probably either in your date subtraction or in your assignment of the grade. 

    I've found it works best to use the DATEDIFF() function instead of simply subracting like you might in certain database queries. BETWEEN doesn't always work well in beast modes, either.

    String outputs should also be surrounded by single quotes.

    CASE

    WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 2 THEN 'A'

    WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 3 THEN 'B'

    WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 4 THEN 'C'

    ...

    END

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    It's probably either in your date subtraction or in your assignment of the grade. 

    I've found it works best to use the DATEDIFF() function instead of simply subracting like you might in certain database queries. BETWEEN doesn't always work well in beast modes, either.

    String outputs should also be surrounded by single quotes.

    CASE

    WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 2 THEN 'A'

    WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 3 THEN 'B'

    WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 4 THEN 'C'

    ...

    END

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user03552
    user03552 Member

    Good to know!

     

    And thank you for the help

Categories