Case within case in beast mode

user051632

Hello, can someone help me with an issue with beat mode - I am able to successfully validate the formula in the beast mode, however when I use the calculated column to the chart, it throws an erro - would you'll know why this happens?

 

(CASE when DAYNAME(WEEKDAY(DATE(`itm_snapshot_date`)))IN ('Saturday') and SUM(`ext_sls_a`)/ COUNT((CASE when `listing_status`='APPROVED' AND `inventory`=0 then 'Live OOS items' when `listing_status`='APPROVED' AND `inventory`>0 then 'Live Purchasable items' else 'Pending, Rejected, Suspended' end)) then 'Prod' else 'No' end )

jaeW_at_Onyx

There's a lot of ... not great things going on here.

 

DAYNAME(WEEKDAY(DATE(`itm_snapshot_date`)))

This is a really verbose way to get the name of the day.  I recommend looking at the DATE_FORMAT function to simplify.  

Also DayName is functionally the same as Weekday, so use one or the other, but don't run your function through both.  Also, look at the definition of Dayname, it expects a Date column.  You are passing it an integer (weekday)

 

 

... (DATE(`itm_snapshot_date`)))IN ('Saturday')  using IN instead of = is less efficient during query execution.  You just have one value so Weekday = "Saturday" will be faster.

 

THE ROOT PROBLEM

WHEN

CASE when DAYNAME... = ('Saturday') and SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' ELSE 'No'

END

 

so you're jumping in and out of aggregated data in the same beast mode.  

If you just had a dataset with 1M rows that aggregates down to 10 rows and you write

CASE when DAYNAME... = ('Saturday') THEN 'Prod'...

then you'd evaluate your function at the row level (before aggregations like SUM have been applied to a dataset.

 

When you write 

CASE WHEN SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' 

Then you're performing the CASE operation on the results of an aggregated dataset SUM()s and COUNTs().  In other words, you're CASE statement is being evaluated against the 10 row aggregation.

 

What you can't do is you can't combine the math on Weekday and SUM() because they exist in different states of the data (pre / post aggregation).

 

WHAT YOU MUST DO

wrap date in a MAX() function. CASE when WEEKDAY(DATE(MAX(`itm_snapshot_date`))) = 7 ...

jaeW_at_Onyx

There's a lot of ... not great things going on here.

 

DAYNAME(WEEKDAY(DATE(`itm_snapshot_date`)))

This is a really verbose way to get the name of the day.  I recommend looking at the DATE_FORMAT function to simplify.  

Also DayName is functionally the same as Weekday, so use one or the other, but don't run your function through both.  Also, look at the definition of Dayname, it expects a Date column.  You are passing it an integer (weekday)

 

 

... (DATE(`itm_snapshot_date`)))IN ('Saturday')  using IN instead of = is less efficient during query execution.  You just have one value so Weekday = "Saturday" will be faster.

 

THE ROOT PROBLEM

WHEN

CASE when DAYNAME... = ('Saturday') and SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' ELSE 'No'

END

 

so you're jumping in and out of aggregated data in the same beast mode.  

If you just had a dataset with 1M rows that aggregates down to 10 rows and you write

CASE when DAYNAME... = ('Saturday') THEN 'Prod'...

then you'd evaluate your function at the row level (before aggregations like SUM have been applied to a dataset.

 

When you write 

CASE WHEN SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' 

Then you're performing the CASE operation on the results of an aggregated dataset SUM()s and COUNTs().  In other words, you're CASE statement is being evaluated against the 10 row aggregation.

 

What you can't do is you can't combine the math on Weekday and SUM() because they exist in different states of the data (pre / post aggregation).

 

WHAT YOU MUST DO

wrap date in a MAX() function. CASE when WEEKDAY(DATE(MAX(`itm_snapshot_date`))) = 7 ...