Data model and expression puzzle

[jduluc12]

Here is the data

[Item]:

LOAD * INLINE [

    ItemID

    1

    2

    3

    4

    5

];

[ItemAssm]:

Load * Inline [

  ItemID, AssmeblyId, QtyPerAssm

  1, 4, 2

  1, 5, 3

  2, 4, 4

  3, 5, 4

  3, 4, 2

  ];

[Txn]:

Load * inline [

  ItemID, QtySold

  1, 4

  2, 4

  3, 5

  4, 3

  5, 6

  5, 6

  1, 5

  2, 6

  3, 5

  4, 8

];

I need to find out that total QtySold for each item.

The total QtySold will be found for each item as individual sales + (sales of its Assmebly * QtyPerAssm)

for example

for itemID 1

The total sale would be

Total individual QtySold + Total QtySold in its Assmebly

Total individual QtySold is (4 + 5) (Hint: see the red text)

and

Total sales of its Assembly * QtyPerAssmembly

The Assemblies of the item 1 are 4 & 5 (Hint: see the text in pink)

QtySold of ItemID 4 & 5 are respectively (3+8) and (6+6) (Hint : see the text in green)

So the sale of ItemID 1 in its Assemblies will be (Sales of its Assemblies * QtyPerAssm)

So it will be

(3+8)*2 + (6+6)*3

So the total QtySold of ItemID will be

Total individual QtySold (4 + 5)  + Total sales of its Assembly * QtyPerAssmembly ((3+8)*2 + (6+6)*3 )

= 9 + 22 + 36

We have the flexibility of changing the data model or using any kind of script in the expression.

Thanks in advance!