Best Of

Unfortunately there's currently no way to do this in a way that will dynamically adapt to the actual set of unique Requestor Organizations. If you can tolerate enumerating the Requestor Organizations explicitly, then this solution works:

In the "upper" section, all we're doing is creating a cross-join table of all the possible combinations of Requestor Organization and Phase, with a fake Project Id of -1. We then append this fake data onto our real data. This ensures that in the Group By, where we group by Requestor Organization+Phase, we end up with a group for every combination, even those combinations that didn't actually appear in the input (e.g. Phase=Cancelled, Requestor Organization=DES).

Here's what the preview data should look like at the Project ID Const tile:

And, in case it's helpful, here is what the Cross Join configuration looks like:

(As I'm writing this, it occurs to me that we could save a tile by just using the same constant for both the cross join key and the dummy Project Id.)

The aggregation we use is Count Distinct on the Project ID column, and we call it Project Count. Then, to correct for our dummy -1 Project Id, we just subtract 1 from the Project Count using an Add Formula tile. Finally, we pivot.

Here is the configuration for the Group By:

And the Pivot:

The pivot is where we have to enumerate all the Requestor Organizations, as I mentioned. This is unfortunate because any changes to the set of Requestor Organizations will require modifications to the flow.

The Add Constant that comes right before the Append should add a constant "Project Id" with value -1. The goal is to create a table that has three columns that all correspond to columns in the original table, Requestor Organization, Project Id, and Phase.

You've got everything up to the Join correct, that should be giving you a table of every possible unique combination of Req Org and Phase. Now we just need to give it a Project Id, and we need it to be obviously fake (not collide with any real Project Id) so we use -1. You could even omit the Add Constant before the Append and just use one of the -1 constants that you used for the Join, but you would have to make sure it was called Project Id so it Appends properly to that column in the original table (remember, Magic ETL Appends work off of column names, not column positions). But the way I have done it in my original example is with that Add Constants tile called ProjectID Const. When you've got that in, you should be able to see the a preview of that tile's data that looks like the screenshot from my first comment:

ProjectID Const configuration:

Append Rows configuration:

Note that yours may not say "No changes", because your real data might have more columns than just those three we've been talking about. The important thing is that it doesn't show any columns that will be dropped, or any columns added to the original input.

Also, since this is missing from my original comment, here is the configuration for the "Correct for Dummy" Add Formula tile:

Hi yes, per ProjectId is what I'm looking for, and again, sorry if I explained that poorly! Each ProjectId could appear in the dataset dozens of times, but each ProjectId will have the same Yearly Total RUN Costs for Live and the same Yearly Total RUN Costs for Nov 2023, so I only needed to identify a value for each ProjectId and make a Nov 2023 column and a Live column

Everything you've helped me with today is enough knowledge gained to build more ETL, more datasets, and many more cards. Thank you!

And I appreciate you replying to my other topic also; I will try to tackle that tomorrow, but I will no doubt have more questions, as there are many things there I haven't used before; only started using ETL this month! :)

What's happening there is pretty weird, I'll try to explain it:

When you write a Group By formula, Magic ETL allows you to reference columns both inside an aggregate function (like SUM()) and outside one. When you reference a column inside an aggregate, that reference is evaluated for every row to produce the input to that function. But when you reference a column outside an aggregate function, it is only evaluated once for every group, for the first value. What you have there is really this:

CASE WHEN FIRST_VALUE(RecordType) = 'Live' THEN SUM(PlannedCost) END

Because the first value of RecordType is actually Historical, this just evaluates to null.

What you really want is this:

SUM(CASE WHEN RecordType = 'Live' THEN PlannedCost END)

Sure, add an ELSE 0 to the case statement.

The issue is the comparison with that string, 'Nov 22, 2023 12:00:00 AM'. That comparison will first convert the left hand side to a string according to the Timestamp Format setting or the default timestamp format, which would be '2023-11-22T00:00:00'. Then it will perform the comparison on the strings, and they won't be equal (unless your timestamp format setting does match that format you used in the string constant, but I assume it doesn't).

This behavior is unique to Magic ETL. Most other SQL implementations will either produce an error when you attempt to compare a timestamp with a string, or convert the string to a timestamp for the comparison rather than the timestamp to a string.

I think what you probably want to do is compare these as Dates, which would look like this:

DATE(BatchTimestamp) = DATE('2023-11-22')