Need help with specific code that is not working in either beast mode or magic etl formula tile

Utz

Hi the following code I am using as a filter on my card and have it set to "on list" its called skus to be purged

"CASE
WHEN On Order = 0
AND (Future Demand = '' OR Future Demand IS NULL)
AND (Future Books = '' OR Future Books IS NULL)
AND (Reserved + WMAVAIL <= 10 AND WMAVAIL > 0)
AND WAREHOUSE_CODE = 11
THEN 'On List'

ELSE 'Not On List'

END"

I have one more beast mode calculation called FBA/WFS that is not a filter but a field within my card itself I have created this calculation to identify whether or not the SKU coming in this filtered list is also in warehouse 18 or 19. Here is the code for

"CASE
WHEN SUM(CASE WHEN WAREHOUSE_CODE IN (18, 19) THEN 1 ELSE 0 END) > 0
THEN 'Exists'
ELSE 'Does Not Exist'
END"

When I bring this FBA/WFS field into my card I am only getting "does not exist" I know my original filter for SKUs to be purged only has warehouse code 11 coming in and in my dataflow I am grouping by on SKU and warehouse code to get one SKU and one warehouse code per line. Is there any way where I can use the filter for SKUs to be purged which is filtering on warehouse 11 only but can also see if any of those SKUs from warehouse 11 are in warehouses 18 and 19 because there are SKUs with warehouses 11, and 18 and 19 in the dataset itself but not sure how I can display it on the card. I have also tried to add a formula in the magic ETL formula tile but that didn't work as well.

DavidChurchman

I think you can do this with a FIXED function: https://domo-support.domo.com/s/article/4408174643607?language=en_US

It always takes me at least three attempts before I correctly write a FIXED function, so this probably won't work out of the box, but it will be something like this:

CASE

WHEN SUM(CASE WHEN WAREHOUSE_CODE IN (18, 19) THEN 1 ELSE 0 END) FIXED(BY WAREHOUSE_CODE FILTER NONE) > 0

THEN 'Exists'

ELSE 'Does Not Exist'

END

DavidChurchman

Because I'm so bad with FIXED formulas, I would personally probably approach this at the ETL step. If you do your FBA/WFS in the ETL before filtering the "On list", it should work.

Utz

CASE

WHEN SUM(CASE WHEN WAREHOUSE_CODE IN (18, 19) THEN 1 ELSE 0 END) FIXED(BY WAREHOUSE_CODE FILTER NONE) > 0

THEN 'Exists'

ELSE 'Does Not Exist'

END

When I am using this calculation in beast mode I am receiving this error "An issue has occurred during processing. We are unable to complete the request at this time - 'undefined'"

Do you know where I would do this in magic ETL I tried the same calculation in the formula tile and it gave me this error "Syntax error in expression at character 65 on line 4: FIXED"

MichelleH

@Utz Fixed functions require the aggregate function to be listed twice like this:

CASE 
  WHEN SUM(SUM(CASE WHEN WAREHOUSE_CODE IN (18, 19) THEN 1 ELSE 0 END)) FIXED (BY WAREHOUSE_CODE FILTER NONE) > 0 
  THEN 'Exists' 
ELSE 'Does Not Exist' 
END

Also note that you can only use FIXED functions in Beast Mode, not Magic ETL. If you wanted to accomplish the same thing in ETL you can use the following steps:

1. Use a Group By tile, grouped by warehouse code to find the sum
2. Join the Group By back to the previous tile on warehouse code to add the summed column to the rest of the data
3. Use a Formula tile for the rest of the case statement (this last step can be done in beast mode as well)

ggenovese

According to the FAQ for Fixed functions, they can't be used to filter a card. I recommend going the ETL route that @MichelleH suggested instead

Utz

Thank you for the help appreciate it will give this a shot