Return values with condition on another column

user003049
user003049 Member
in Archive

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.

Comments

  • Valiant
    Valiant Coach

    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
    user003049 Member

    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."

Categories