How to count occurrences across multiple columns

ThomasR

I want to count the number of times a value occurs in two different columns

my dataset is set up in a way that an order will always have two IDs in the same row. The IDs can repeat in the same column or be in another column, but they will never be in the same row.

In the example below, the number of occurrences for "1" should be 3.

Is this possible to accomplish using Beastmode?

DavidChurchman

Personally, I would use MagicETL to unpivot the data to be this structure:

Order | ID # | ID

111 | ID 1 | 1

111 | ID 2 | 7

112 | ID 1 | 2

112 | ID 2 | 6

That would make counting the IDs trivial in the card, and you can pivot by ID # to get the same table.

If you can't use MagicETL, then depending on the structure of your IDs, I think you could concat your two ID columns and check if ID1 is contained within that concatenated column.

sum(

case

when concat(ID1, '-', ID2) like concat('%', ID1, '%') then 1 else 0

end

)

But the danger of this approach is if you have IDs that are contained within other IDs. (Like if you have both an ID of 1 and ID of 11). If that's the case, you'd want to do something like padding your IDs with zeros to prevent this before concatenating them.

ArborRose

SUM(

CASE WHEN ID1 = 1 THEN 1 ELSE 0 END +

CASE WHEN ID2 = 1 THEN 1 ELSE 0 END

)

ThomasR

I can see how that would work if I only had a few IDs, but if I was working with a few thousand IDs is there a way to do this that runs all the IDs without having to do the above for each one?

DavidChurchman

Personally, I would use MagicETL to unpivot the data to be this structure:

Order | ID # | ID

111 | ID 1 | 1

111 | ID 2 | 7

112 | ID 1 | 2

112 | ID 2 | 6

That would make counting the IDs trivial in the card, and you can pivot by ID # to get the same table.

If you can't use MagicETL, then depending on the structure of your IDs, I think you could concat your two ID columns and check if ID1 is contained within that concatenated column.

sum(

case

when concat(ID1, '-', ID2) like concat('%', ID1, '%') then 1 else 0

end

)

But the danger of this approach is if you have IDs that are contained within other IDs. (Like if you have both an ID of 1 and ID of 11). If that's the case, you'd want to do something like padding your IDs with zeros to prevent this before concatenating them.