Can we use Partition by function with Case statement in beast mode?

Salmas
Salmas Member
in Charting

Hi,

I wanted to achieve the below screenshot data via beast mode.

image.png






For each token id, whichever column (B,C,D) has non zero value, in the Merged column, I wanted to get that value. I tried case statement, say

Case when A=0 and B=0 then C

when A=0 and C=0 then B

when B=0 and C=0 then A end

This is not giving me the correct output. Looking for a solution.

Thanks in advance

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Salmas

    In your example for Token ID 108 you have two records - Are you wanting to keep these records separate or should they be merged into a single row?

    Assuming they're separate rows you can use COALESCE and NULLIF:

    COALESCE(NULLIF(`A`, 0), NULLIF(`B`, 0), NULLIF(`C`, 0))


    NULLIF returns null if the column given is 0

    COALESCE returns the first non-null value it finds in the given list of parameters

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Salmas

    In your example for Token ID 108 you have two records - Are you wanting to keep these records separate or should they be merged into a single row?

    Assuming they're separate rows you can use COALESCE and NULLIF:

    COALESCE(NULLIF(`A`, 0), NULLIF(`B`, 0), NULLIF(`C`, 0))


    NULLIF returns null if the column given is 0

    COALESCE returns the first non-null value it finds in the given list of parameters

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Salmas
    Salmas Member

    Hi @GrantSmith

    I wanted to keep both the rows separate. Also I checked for COALESCE function is beast mode, but it's not available for me

  • GrantSmith
    GrantSmith Coach

    Did you try and run the beast mode and it said it couldn’t find it or are you looking for it in the list?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Salmas
    Salmas Member

    @GrantSmith

    It's working. The function wasn't showing in the beast mode, but I tried running it. It's validating.

    Thank you

Categories