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

[hellyars]

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..

Hierarchy	Yr1	Yr2	Yr3	Yr4	Yr 5	Yr6	Yr7
Child of #1	5	10	10	15	10	5	5
Child of #1	5	5	5	5	5	10	5

Compared to the Parent

Hierarchy
Parent Line #1	10	15	15	30	30	30	30

How do I generate this...

Hierarchy	Yr1	Yr2	Yr3	Yr4	Yr5	Yr6	Yr7
Child #1	5	10	10	15	10	5	5
Child #2	5	5	5	5	5	10	5
New Child #3	0	0	0	10	15	15	20

[BenMoss · Accepted solution]

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 solution]

Attaching a sample workflow for you problem...