Bom Calculation

[Anonymous]

Hi everyone!

I have these two tables:

  table1:

ItemNumber	Type	101_Q	101_A	102_Q	102_A	Total_Q	Total_A	104_Q	104_A	201_Q	201_A	202_Q	202_A	203_Q	203_A
A100	P	11	11	12	8	23	19	3	0	3	0	12	0	13	0
A101	P	13	12	13	13	26	25	11	0	4	0	2	0	11	0
A102	P	2	3	2	9	4	12	6	0	12	0	6	0	12	0
A103	P	7	10	9	12	16	22	14	0	8	0	15	0	2	0
B100	M	1	5	5	20	6	25	3	0	5	0	5	0	3	0
B200	M	2	10	5	30	7	40	2	0	4	0	4	0	3	0
B300	M	5	5	2	4	7	9	3	0	5	0	2	0	2	0
B400	M	5	4	5	5	10	9	3	0	4	0	4	0	5	0

table2:

ItemNumber	Type	ItemNumber	Q
A100	P	B100	1
A100	P	B200	2
A101	P	B100	2
A101	P	B200	1
A101	P	B300	3

the first task which I've done is to calculate the 104_A to 203_A. the second one which is complicated is to calculate the cost of P type through its BOM(table2) . the answer should be like this:

ItemNumber	Type	ItemNumber	Q	TotalA/TotalQ	Cost
A100	P	B100	1	4.166667	4.166667
A100	P	B200	2	5.714286	11.42857
A101	P	B100	2	4.166667	8.333333
A101	P	B200	1	5.714286	5.714286
A101	P	B300	3	1.285714	3.857143

my QV file is attached here, how to tackle this problem?

[Anonymous]

my excel file is attached here

[Anonymous]

No one can help me? any solution?

[sunny_talwar · Accepted solution]

Script

Table:

CrossTable(Transaction, QA)

LOAD ItemNumber,

    [101_Q],

    [101_A],

    [102_Q],

    [102_A],

    Total_Q,

    Total_A,

    [104_Q],

    [104_A],

    [201_Q],

    [201_A],

    [202_Q],

    [202_A],

    [203_Q],

    [203_A]

FROM [6.xlsx]

(ooxml, embedded labels);

FinalTable:

LOAD *,

SubField(Transaction, '_', 1) as Transaction_First_Part

Resident Table;

DROP Table Table;

Table:

LOAD ItemNumber,

    Type,

    ItemNumber1,

    Q

FROM

[6.xlsx]

(ooxml, embedded labels, table is BOM);

Left Join (Table)

LOAD ItemNumber as ItemNumber1,

Transaction as New_Transaction,

QA as New_QA

Resident FinalTable

Where Match(Transaction, 'Total_Q', 'Total_A');

Chart expressions

=Sum({<New_Transaction = {'Total_A'}>}New_QA)/Sum({<New_Transaction = {'Total_Q'}>}New_QA)

=Sum({<New_Transaction = {'Total_A'}>}New_QA)/Sum({<New_Transaction = {'Total_Q'}>}New_QA) * Q

[Anonymous]

Thank you so much, Could you please the QV file?

[sunny_talwar]

Open this link... it's already attached

Re: Bom Calculation

[Anonymous]

thank you so much, you are a perfect man in Qlikview, I admire you.

[sunny_talwar]

thank you for your admiration my friend.

Best,

Sunny