Aggregation

[bella21]

Can you aggregate and join datasets from your library? E.g. join source data to current data, sum the source data based on key field from current data? Similar to a sum and join in SQL.

[marthamiller_SE]

It's not exactly in one step, but one feature that you may not be aware of is the ability to "expand lookup steps".  Once you do a lookup, open the Steps panel on the left and click "expand lookup steps" right under the Lookup step.  From here, you can click on the Import step and apply changes including hiding columns and group by which are only applied to the dataset that you are looking up against - in this way you can alter the dataset coming into the project before the actual lookup operation.  I hope this tip is helpful!

[bella21]

Thanks for responding!  I have taken that approach, but I would be more interested in combining those into one step in a project where you join and sum at the same time. Again, similar to sum/join in SQL or SUMIFS formula in Excel. I believe this would also require the ability to select specific columns to bring in on the lookup/join instead of bringing the entire source data set into the current data set.  I'll check out the community videos and see if I can pick up anything there.

[marthamiller_SE]

Yes, Paxata supports both lookup and true join operations via the Attach-Lookup toolbar option in righthand tools menu - just click the paperclip icon and select lookup to identify the dataset that you want to join into your project. You can then select columns to join on or let Paxata find the best matches. Paxata also supports aggregation through the Shape - Group By feature. When you select "Shape" from the righthand Tools menu (diagonal arrows 3 from bottom), the default Shaping operation is Group By which allows you to select columns to group by as well as columns to aggregate by sum and other functions. For more information, the "Getting Started" section of the Community has tutorial videos on Combining and Shaping data.