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!
