Data model and expression puzzle

jduluc12 — Mon, 20 Mar 2017 02:24:16 GMT

Here is the data

[Item]:

LOAD * INLINE [

ItemID

];

[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

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!

topic Data model and expression puzzle in QlikView

Data model and expression puzzle