topic Re: Bom Calculation in QlikView

Bom Calculation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Bom Calculation

sunny_talwar — Sun, 13 May 2018 18:08:34 GMT

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?

Re: Bom Calculation

Anonymous — Sun, 13 May 2018 18:10:45 GMT

Hi Dear Sunny Talwar

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

Re: Bom Calculation

sunny_talwar — Sun, 13 May 2018 18:13:08 GMT

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

Re: Bom Calculation

Anonymous — Sun, 13 May 2018 18:13:42 GMT

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

Re: Bom Calculation

Anonymous — Sun, 13 May 2018 18:17:34 GMT

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?

Re: Bom Calculation

sunny_talwar — Sun, 13 May 2018 18:23:58 GMT

Is this something you can do in the script?

Re: Bom Calculation

Anonymous — Sun, 13 May 2018 18:24:59 GMT

Yes I really can

Re: Bom Calculation

Anonymous — Sun, 13 May 2018 19:10:55 GMT

Sorry Sunny Talwar

You find the solution?

Re: Bom Calculation

sunny_talwar — Sun, 13 May 2018 21:41:13 GMT

Would you be able to share the Excel file?

Re: Bom Calculation

Anonymous — Mon, 14 May 2018 02:56:19 GMT

my excel file is attached here

Re: Bom Calculation

Anonymous — Mon, 14 May 2018 08:32:14 GMT

No one can help me? any solution?

Re: Bom Calculation

sunny_talwar — Mon, 14 May 2018 11:21:54 GMT

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,

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

Re: Bom Calculation

Anonymous — Mon, 14 May 2018 11:25:29 GMT

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

Re: Bom Calculation

sunny_talwar — Mon, 14 May 2018 11:26:37 GMT

Open this link... it's already attached

Re: Bom Calculation

Anonymous — Mon, 14 May 2018 11:31:15 GMT

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

Re: Bom Calculation

sunny_talwar — Mon, 14 May 2018 11:32:09 GMT

thank you for your admiration my friend.

Best,

Sunny