Return values with condition on another column

Hi, I would like some help with beast mode as I'm very new to this.

I have a list of keywords, URLs and their amount.
There are duplicate keywords with different urls, one containing "/blah/" and one not.

Keyword URL amount
AAA xyz.com/aaa/blah/ 1
BBB xyz.com/bbb/blah/ 2
CCC xyz.com/ccc/blah/ -
AAA xyz.com/aaa/ -
BBB xyz.com/bbb/ -
CCC xyz.com/ccc/ 2

I would like domo to return the amount for URLs containing "/blah/,"

but if the amount is "-", return the amount for URLs not containing "/blah/".

In the above example I would be getting:

AAA 1

BBB 2

CCC 2

Does this make sense?

I know the latter is not correct, if someone can walk me through this I would be very grateful.

(CASE WHEN (`URL` = '%/blah/%') AND (`amount` = '-') THEN `amount` WHEN (`URL` NOT '%/blah/%') ELSE END)

or if there is another way to get around this, I'm all ears.

Find more posts tagged with

Comments

BlueRooster

It might go something like this:

CASE WHEN `URL` like '%blah%' AND `amount` > 0 THEN `amount` 
           WHEN `URL` NOT LIKE '%blah%' AND `amount` > 0 THEN `amount`
END

Is the '-' an actual dash or is this a Null? If NULL change the `amount` >0 to `amount` IS NOT NULL

Let me know if you have any questions,

ValiantSpur

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

user003049

Thank you very much ValiantSpur.
The "-" is an actual dash (word), not null, so the calculation you have written seems to fit.

I'm stuck with another problem though, I can save the beast mode calculation with the "Share Calculation on DataSet" checkbox checked, and I can see it in the "measures" property, but when I put it in a filter and hit "save" the filter disappears.

I read a similar question regarding this problem but I don't quite understand where the "top layer of the card" means. (I am saving the beastmode inside a card analyzer.)

Can anybody help me with this?

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Beast-mode-calculations-disappearing-after-saving-card/m-p/23550

"Because drill path cards are not actual cards, BeastModes that are created in the Drill Path will not be saved. In order to ensure the BeastModes are saved, they must be created in the top layer of the card, and have the "Share Calculation on DataSet" checkbox checked. This is the only way to ensure that BeastModes will be saved and checking the box will make them accessible in the drill path cards. Please let me know if you have any questions."