Beast Mode

carthur

Hello all,
I need a little bit of help with this beast mode.

I have locations and flags indicator in the dataset. All i am trying to achieve using IN function is, if locations are in A,B,C and flags are Y then HUB else NOT HUB. I just want to bucket HUBS and NON HUBS. The issue there are some that may have both.

In other locations other than ABC I am using different column so its a little easier just like below.

How do I add something similar to the ifnull for HWV please to the IN statement above it.
Any ideas please?

ColemenWilson

So does this not work for you:
CASE WHEN `LOCID` IN('A','B','C') AND `flags` = 'Y' THEN 'HUB'
WHEN `LOCID` = 'HWV' AND IFNULL(`PROD_CD`,'999') IN ('RSALE') THEN 'HUB'
ELSE 'Not HUB' END

What issue are you running into? The IFNULL isn't really necessary since you are only looking for values = 'RSALE' in your case statement.

GrantSmith

Can you expand on why you're wanting something similar to IFNULL in your first condition? Do you want to include the AMT if either flag is set to Y? Can you give an example record which would fall into the scenario you're talking about?

marcel_luthi

Correct, is just a matter of priority and knowing what should take precedence (in this case as they are mutually exclusive is not that important, but is always good practice to think of it this way, so you can create WHEN statements in the right order to achieve the desired outcome).

Agree with @GrantSmith, having a couple of examples illustrating the different scenarios and where if you'd expect them to be bucketed will help getting a better understanding on what the underlying logic needs to be. (There should only be a couple of possible combinations you need to account for, and basically you only need to do so for one of the two possible outcomes, and leave the other as the default, which depends on which requires the least amount of combinations and how the definitions might change over time).

carthur

@ColemenWilson @GrantSmith @marcel_luthi Thanks for looking at this for me.

I would like to visualize the data like the table below. I want to see their amounts under conversion and amounts for the same customer under FO and therefore writing the beast mode calc, I should be able to split the amounts in order to put them on the same line. So if flag is Y then FO else is CO but building it with the IN function or a better way of doing it.

ST_-Superman-_

@carthur

Thanks for posting the desired output. It would be helpful to see what the actual data looks like in your dataset and what you want Domo to do with those values to populate the table.

Any chance you could walk through an example with some data points that match the schema of your dataset? And how you would expect those values to be displayed in your table?

carthur

@ST_-Superman-_ So for example we may have a client called AXIS. They order from us a pencil as one of their products.
We manufacture some part of the pencil in our plant and order the erazer or rubber from another company.
In our data, we will have the cost of ordering the erazer from another company and the cost to us manufacturing the pencil itself. I want to be able to capture for AXIS on one line how much it costs us to manufacture the pencil stem in Column A on the table above, the part we ordered from somewhere in column B and then total in column C.

marcel_luthi

Based on your description, this sounds like a fairly good example of when you could use a Pivot Table. The tricky part is to have a well defined way of identifying a cost as either Manufacture or Acquisition (A and B), then you can show totals for the Pivot table, which would be your column C.

In this way, all your Beast Mode needs to be able to do is flag each cost line as one of the two options above. Of course this is working under the assumption that the Cost is stored in the same column regardless of it being manufacture or acquisition.