coalescing data in a table

NathanDorsch · 2022-07-22T15:45:40+00:00

There was an error rendering this rich post.

I'm attempting to build a table, which pulls from a order history dataset and includes the following key columns: Account Number, Order Src (CSR, EDI, or DCX).

When I create beastmodes (e.g. CSR $ = case when `Order Src` like 'CSR' then sum (`order_line_total_price`) end) to break down the orders for each account by their order sources... it doesn't include them all on one line for each account.

As shown below, you can see for Account # 12000001244... it has three different rows (one for each CSR, DCX, and EDI). How can I get all of them on one row for this account (and all the other accounts)?

Find more posts tagged with

Accepted answers

All comments

GrantSmith

You need to put your condition inside your aggregation.

SUM(CASE WHEN `Order Src` LIKE 'CSR' THEN `order_line_total_price`) END)

Also - as a side note your LIKE 'CSR' isn't matching on any wild cards so it's expecting Order Src to be equal to exactly CSR. if you're wanting to see if CSR is anywhere inside the Order Src value then you'd need to put in the wildcards

LIKE '%CSR%'

You'll also need to make sure you aggregate the other columns you wish to summarize for each account

NathanDorsch

Thanks. when I removed the last order (by each source) beastmodes, this worked.

However, I have the following beastmodes for each source: Last CSR Order = case when `Order Src` like 'CSR' then max(`order_date`) end

And when I include them, they seem to mess up the table... is there a way to fix that formula so the issue doesn't arise?

RobSomers

Whenever you want to do an aggregation you need to put your function on the outside of your case when statement. If you do case when 'x' then max(date), the beast mode just does the calculation for each individual line, so it will just take the max date of each row in your dataset. If you instead do MAX(case when 'x' then 'date') then the beast mode will treat that as an aggregation and will look for the MAX date in your table groupings.

NathanDorsch

thanks guys!