Syntac Error: Trying to group days into weeks using Case Function

C_Mathur
C_Mathur
in Dataflows

Hi, I've been trying to group days to represent buckets of weeks. I code I've used is as follows:

 

Case

when `accepted_date` between '01/01/2018' and '09/06/2019'

then '1 Wk Sp'

when `accepted_date` between '09/07/2019' and '09/13/2019'

then '2 Wk Sp'

when `accepted_date` between '09/14/2019' and '09/20/2019'

then '3 Wk Sp'

when `accepted_date` between '09/21/2019' and '09/27/2019'

then '4 Wk Sp'

when `accepted_date` between '09/28/2019' and '10/04/2019'

then '1 Wk Oct'

when `accepted_date` between '10/05/2019' and '10/11/2019'

then '2 Wk Oct'

when `accepted_date` between '`10/12/2019' and '10/18/2019'

then '3 Wk Oct'

when `accepted_date` between '10/19/2019' and '10/25/2019'

then '4 Wk Oct'

when `accepted_date` between '10/26/2019' and '11/01/2019'

then '5 Wk Oct'

when `accepted_date` between '11/02/2019' and '11/08/2019'

then '1 Wk Nv'

when `accepted_date` between '11/09/2019' and '11/15/2019'

then '2 Wk Nv'

when `accepted_date` between '11/16/2019' and '11/22/2019'

then  '3 Wk Nv'

when `accepted_date` between '11/23/2019' and '11/29/2019'

then '4 Wk Nv'

when `accepted_date` between '11/30/2019' and '12/06/2019'

Then '1 Wk Dc'

when `accepted_date` between '12/07/2019' and '12/13/2019'

Then '2 Wk Dc'

when `accepted_date` between '12/14/2019' and '12/20/2019'

Then '3 Wk Dc'

when `accepted_date` between '12/21/2019' and '12/27/2019'

Then '4 Wk Dc'

when `accepted_date` between '12/28/2019' and '01/03/2020'

Then '1 Wk Ja'

when `accepted_date` between '01/04/2020’ and ’01/10/2020'

Then '2 Wk Ja'

when `accepted_date` between '01/11/2020' and '01/17/2020'

Then '3 Wk Ja'

when `accepted_date` between '01/18/2020' and '01/24/2020'

Then '4 Wk Ja'

when `accepted_date` between '01/25/2020' and '01/31/2020'

Then '1 Wk Fb'

when `accepted_date` between '02/01/2020' and '02/07/2020'

then '2 Wk Fb'

when `accepted_date` between '02/08/2020' and '02/14/2020'

Then '3 Wk Fb'

when `accepted_date` between '02/15/2019' and '02/21/2019'

Then '4 Wk Fb'

when `accepted_date` between '02/22/2020' and '02/28/2020'

Then  '1 Wk Mr'

when `accepted_date` between '02/29/2020' and '03/06/2020'

then  '2 Wk Mr'

when `accepted_date` between '03/07/2020' and '03/13/2020'

then '3 Wk Mr'

when `accepted_date` between '03/14/2020' and '03/20/2020'

Then '4 Wk Mr'

when `accepted_date` between '03/21/2020' and '03/27/2020'

Then '5 Wk Mr'

when `accepted_date` between '03/28/2020' and '04/03/2020'

Then '1 Wk Ap'

when `accepted_date` between '04/04/2020' and '04/10/2020'

then '2 Wk Ap'

when `accepted_date` between '04/11/2020' and '04/17/2020'

then '3 Wk Ap'

when `accepted_date` between '04/18/2020' and  '04/24/2020'

then '4 Wk Ap'

when `accepted_date` between '04/25/2020' and '05/01/2020'

then '1 Wk My'

when `accepted_date` between '05/02/2020' and '05/08/2020'

Then '2 Wk My'

when `accepted_date` between '05/09/2020' and '05/15/2020'

then '3 Wk My'

when `accepted_date` between '05/16/2020' and '05/22/2020'

then '4 Wk My'

when `accepted_date` between '05/23/2020' and '05/29/2020'

then '1 Wk Jn'

when `accepted_date` between '05/30/2020' and '06/05/2020'

then '2 Wk Jn'

when `accepted_date` between '06/06/2020' and '06/12/2020'

then '3 Wk Jn'

when `accepted_date` between '06/13/2020' and '06/19/2020'

then '4 Wk Jn'

when `accepted_date` between '06/20/2020' and '06/26/2020'

then '5 Wk Jn'

when `accepted_date` between '06/27/2020' and '07/03/2020'

then '1 Wk Jy'

when `accepted_date` between '07/04/2020' and '07/10/2020'

then '2 Wk Jy'

when `accepted_date` between '07/11/2020' and '07/17/2020'

then '3 Wk Jy'

when `accepted_date` between '07/18/2020' and '07/24/2020'

then '4 Wk Jy'

when `accepted_date` between '07/25/2020' and '08/01/2020'

then '1 Wk Ag'

when `accepted_date` between '08/02/2020' and '08/07/2020'

then '2 Wk Ag'

when `accepted_date` between '08/08/2020' and '08/14/2020'

then '3 Wk Ag'

when `accepted_date` between '08/15/2020' and '08/21/2020'

then '4 Wk Ag'

when `accepted_date` between '08/22/2020' and '08/28/2020'

then '5 Wk Ag'

End

 

Please let me know why this code is showing a syntax error in beast mode.

 

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    Beast modes don't support BETWEEN syntax.

     

    But also... this is the sort of thing that might benefit from a lookup table / date dimension + a fusion.  It'll be more easy to manage.

Categories