Solved: Bom Calculation - Qlik Community

Anonymous · ‎2018-05-13

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?

sunny_talwar · ‎2018-05-14

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

View solution in original post

sunny_talwar · ‎2018-05-13

I am not completely sure I understand what you are trying to do? first of all, do you have 2 things you are trying to accomplish or 1 is done and your trying to do the 2nd now?

Anonymous · ‎2018-05-13

Hi Dear Sunny Talwar

I am trying to do the 2nd one now? Bom calculation or Cost per P Type

sunny_talwar · ‎2018-05-13

How exactly do you perform those calculations? I mean the logic?

Anonymous · ‎2018-05-13

You know, Actually the table 2 should be complicated by TotalA/TotalQ and Cost

Anonymous · ‎2018-05-13

As you look at the table 2, at first we see product A100 uses B100 and B200 material and B100 and B200 price is calculated by Total B100 A/ Total B100 Q. Then we should multiple this price by B100 and B200 quantity.

ItemNumber	Type	ItemNumber	Q	TotalA/TotalQ	Cost
A100	P	B100	1	4.166667	4.166667
A100	P	B200	2	5.714286	11.42857

As it clear?

sunny_talwar · ‎2018-05-13

Is this something you can do in the script?

Anonymous · ‎2018-05-13

Yes I really can

Anonymous · ‎2018-05-13

Sorry Sunny Talwar

You find the solution?

sunny_talwar · ‎2018-05-13

Would you be able to share the Excel file?

Bom Calculation

Related Topics