How do I create a new record based on a comparison and add back to one of the workflows?

Question

My dataset covers a 5 year period.

The parent line has the total for all 5 years.

Each parent is made up of two types of children -- those that have data for all 7 years and those that only have it for 3 years.

The final dataset will only include the children (because they provide more detail).   This means the last 4 years may not equal the total of the parent line.

My question is --> how can I create a new child line that is the sum of the existing children minus the parent line.

The parent and staring children exist in two separate flows.

Children data..

HierarchyYr1Yr2Yr3Yr4Yr 5Yr6Yr7Child of #151010151055Child of #155555105

Compared to the Parent

Hierarchy       Parent Line #110151530303030

How do I generate this...

HierarchyYr1Yr2Yr3Yr4Yr5Yr6Yr7Child #151010151055Child #255555105New Child #300010151520

BenMoss · Accepted Answer

So transpose your first dataset. Your key field should be the field that identifies the parent the child belongs too. Your data fields should be your value fields.

Next perform a summarise of this data, grouping by your parent ID field and summing the value field.

As a separate stream, take your parent only data and transpose this, again the parent ID would be your key field and the data fields are your values.

You now have two datasets to compare. You should join these together on the parent ID field and the name field (which represents the members from your original column headers).

Now you can use a formula tool to calculate the values that will form your final table by simply taking away the two value fields from one another.

Finally use the cross tab field, grouping by the parent ID field, using the ‘name’ field as your header and your new calculated fields as the values. You can then union this to the data that existed in your first table to give you the result.

Unfortunately I am mobile so cannot post the example workflow, but hopefully the description is enough.

Ben

ponraj · Accepted Answer

Attaching a sample workflow for you problem...