Hi Apptians!

Back again with another question

We have a Transform that we have appended to a Master data set and want to sum multiple lines on the Transform to one line on the Master.  Essentially, we have a few columns in mind:

Transform

-Project

-Resource Amount

Master

-Project ID

-Approved Budget

I would like to have the following happen:

Approved Budget = If( {Master.Project ID}={Transform.Project}, Sum(Resource Amount))

Any idea if this is possible to do?

If you need me to clarify, just let me know!

Thanks so much,

Molly

If Transform.Project is currently mapped into Master.Project ID, then one option is to group the Master data table by its Project ID column.

The grouping will automatically sum all numeric-type columns, such as Resource Amount (assuming you've also mapped Transform.Resource Amount into one of the Master table columns).

Hi Chris,

Thanks for your help!

While that would work in most cases, unfortunately ours is a bit different.  We need to have multiple lines per project in our Master data because of the applications they are associated with (one project could be associated with multiple applications).  Therefore, grouping the projects would lose valuable data for us.

Do you, or anyone else out there know of a way to sum as I described above?  Either that, or is there another work around like adding a key that may help this situation?

Thanks!

Molly

In this case I think you'll want to try the SumIf() function, e.g.:

Master.Summed Project Amount = SumIf(Project ID, Project ID, Resource Amount)

If three Project ID=123 rows exist in Master data table, the three Resource Amount values will be summed into the new Summed Project Amount column.

Hi Chris,

The only issue there is the middle portion- the criteria for the sumif.

So if the equation is =SUMIF(Range,Criteria,Sum_Range), the range and sum_range I know what to put there, but I need the criteria to be when the Project ID in the Master data equals the Project ID in the Transform and it won't let me put that in there.

Thoughts?

Thanks!

In your original post you mentioned having appended Transform to Master.

During the append step, did you map Transform.Project ID to Master.Project ID?

If not, how are you mapping in the project ID (i.e., from where to where)?

Hi Chris-

We have a Projects List Transform that is already mapped and populating the Projects Master.  The Project ID is linked through there.  The issue was that there is no budget information in the Projects List Transform and so we wanted to append the Projects Budget Transform to the Projects Master.  We couldn't get these to sync appropriately, but I believe I have found a work around by linking the two transforms together to pull Budget into the Projects List Transform.  We still need to troubleshoot to see if everything is working appropriately, but I believe we solved the issue!

Thanks for all your help!