Sum of quantity based on relations

[engishfaque]

Dear All,

I'm going to add the quantity of ProductSKU and ChildSKU fields.

For example (real scenario):

relations.xlsx file consist on all the parent and child relations.

In relations.xlsx file, threre is a ChildSKU = "NI-MF-006" with having 7 ParentsSKU, such as illustrated below:

relations.xlsx
parentSKU	ChildSKU
NI-70701	NI-MF-006
NI-70701-ZZ	NI-MF-006
NI-70701-ZZ1	NI-MF-006
NI-70701-ZZ2	NI-MF-006
NI-MF-006-ZZ1	NI-MF-006
NI-R6501-Gadsden35-MF-006-Set3	NI-MF-006
NI-R6501-MF-006-Set2	NI-MF-006
NI-MF-00623	NI-MF-006

salesYTD2014_testing.xlsx file consist on quantity of each individual items (parent and child) such as illustrated below:

prod_sku	sum(quantity)
NI-MF-006	540
NI-70701	13
NI-70701-ZZ	20
NI-70701-ZZ1	58
NI-70701-ZZ2	10
NI-MF-006-ZZ1	141
NI-R6501-Gadsden35-MF-006-Set3	227
NI-R6501-MF-006-Set2	247
NI-MF-00623	82

1. relations.xlsx file consist on relations

2. salesYTD2014_testing.xlsx file consist on quantity

Required Output:Sum of quantity must be sum of all the ParentSKU and ChildSKU in against of NI-MF-006 such as given below.

prod_sku	sumofquantity
NI-MF-006	1338

Kindly find attached files.

Kind regards,

Ishfaque Ahmed

[engishfaque]

Dear Jyothish,

Many thanks, problem has been resolved with your and @tylor cool solution.

Kind regards,

Ishfaque Ahmed