Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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?
Hi Dear Sunny Talwar
I am trying to do the 2nd one now? Bom calculation or Cost per P Type
How exactly do you perform those calculations? I mean the logic?
You know, Actually the table 2 should be complicated by TotalA/TotalQ and Cost
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?
Is this something you can do in the script?
Yes I really can
Sorry Sunny Talwar
You find the solution?
Would you be able to share the Excel file?