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

Salmas

Hi,

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

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

GrantSmith

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

GrantSmith

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

Salmas

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

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?

Salmas

@GrantSmith

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

Thank you