Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I have production orders with specific material numbers (starting with 7). From the quantitiy booked to order and the goods issue quantitiy I need to calculate a factor.
This calcualted factor I need to use further but only for the production orders starting the raw material with a 7.
The factor has to be the average over 12 months backwards without the current month.
So far I have the factor but when I write it like I did, I can't use it for the production orders with the raw material number starting with 7.
What I have:
year | month | material | raw | Sum(faktor) | Sum(EN)*avg(faktor) |
2021 | 1 | 7114866 | 8000543 | 1.0842 | 2233 |
2021 | 2 | 7114866 | 8000543 | 1.0754 | 2316 |
2021 | 5 | 7114800 | 8000640 | 1.0241 | 3042 |
2021 | 6 | 7114800 | 8000640 | 1.0189 | 3633 |
2021 | 9 | 7005533 | 8000640 | 1.0122 | 6705 |
2021 | 10 | 7005533 | 8000640 | 1.0118 | 1740 |
2021 | 1 | 3115867 | 7000691 | 0.0000 | - |
2021 | 2 | 3103262 | 7000701 | 0.0000 | - |
2021 | 3 | 3008286 | 7000543 | 0.0000 | - |
2021 | 3 | 3103261 | 7000701 | 0.0000 | - |
2021 | 4 | 3103253 | 7000701 | 0.0000 | - |
2021 | 4 | 3115651 | 7000640 | 0.0000 | - |
2021 | 5 | 3103253 | 7000701 | 0.0000 | - |
2021 | 6 | 3111643 | 7000712 | 0.0000 | - |
2021 | 7 | 3108532 | 7000712 | 0.0000 | - |
2021 | 7 | 3114800 | 7000640 | 0.0000 | - |
2021 | 8 | 3107014 | 7000712 | 0.0000 | - |
2021 | 8 | 3114800 | 7000640 | 0.0000 | - |
2021 | 9 | 3107014 | 7000712 | 0.0000 | - |
2021 | 10 | 3107014 | 7000712 | 0.0000 | - |
2021 | 11 | 3107014 | 7000712 | 0.0000 | - |
2021 | 11 | 3115815 | 7000690 | 0.0000 | - |
2021 | 12 | 3106928 | 7000712 | 0.0000 | - |
2021 | 12 | 3115814 | 7000690 | 0.0000 | - |
2022 | 1 | 3106928 | 7000712 | 0.0000 | - |
2022 | 1 | 3111639 | 7000713 | 0.0000 | - |
What I need is the average factor (backwards 12 months, without current month for all the orders starting with material 7) - marked purple - and then multiply this factor with the quantitiy from the orders with the raw material starting with 7 - marked green.
year | month | material | raw | Sum(faktor) | Sum(EN)*avg(faktor) |
2021 | 1 | 7114866 | 8000543 | 1.0842 | |
2021 | 2 | 7114866 | 8000543 | 1.0754 | |
2021 | 5 | 7114800 | 8000640 | 1.0241 | |
2021 | 6 | 7114800 | 8000640 | 1.0189 | |
2021 | 9 | 7005533 | 8000640 | 1.0122 | Median (Average(faktor)) |
2021 | 10 | 7005533 | 8000640 | 1.0118 | 1.0378 |
2021 | 1 | 3115867 | 7000691 | 0.0000 | QTY*Median |
2021 | 2 | 3103262 | 7000701 | 0.0000 | QTY*Median |
2021 | 3 | 3008286 | 7000543 | 0.0000 | QTY*Median |
2021 | 3 | 3103261 | 7000701 | 0.0000 | QTY*Median |
2021 | 4 | 3103253 | 7000701 | 0.0000 | QTY*Median |
2021 | 4 | 3115651 | 7000640 | 0.0000 | QTY*Median |
2021 | 5 | 3103253 | 7000701 | 0.0000 | QTY*Median |
2021 | 6 | 3111643 | 7000712 | 0.0000 | QTY*Median |
2021 | 7 | 3108532 | 7000712 | 0.0000 | QTY*Median |
2021 | 7 | 3114800 | 7000640 | 0.0000 | QTY*Median |
2021 | 8 | 3107014 | 7000712 | 0.0000 | QTY*Median |
2021 | 8 | 3114800 | 7000640 | 0.0000 | QTY*Median |
2021 | 9 | 3107014 | 7000712 | 0.0000 | QTY*Median |
2021 | 10 | 3107014 | 7000712 | 0.0000 | QTY*Median |
2021 | 11 | 3107014 | 7000712 | 0.0000 | QTY*Median |
2021 | 11 | 3115815 | 7000690 | 0.0000 | QTY*Median |
2021 | 12 | 3106928 | 7000712 | 0.0000 | QTY*Median |
2021 | 12 | 3115814 | 7000690 | 0.0000 | QTY*Median |
2022 | 1 | 3106928 | 7000712 | 0.0000 | QTY*Median |
2022 | 1 | 3111639 | 7000713 | 0.0000 | QTY*Median |
Here's the script I have so far:
Factor_temp10:
LOAD * INLINE
[
year,month,order,material,raw,EN,MG
2021,1,1530422,7114866,8000543,2060.000,1900
2021,2,1529821,7114866,8000543,2154.000,2003
2021,3,1530421,3008286,7000543,2060.000,1890
2021,4,1531020,3115651,7000640,3600.000,3200
2021,5,1530144,7114800,8000640,2970.000,2900
2021,6,1530215,7114800,8000640,3566.000,3500
2021,7,1530330,3114800,7000640,2010.000,1988
2021,8,1530331,3114800,7000640,3496.000,3456
2021,9,1531021,7005533,8000640,6624.000,6544
2021,10,1531022,7005533,8000640,1720.000,1700
2021,11,1529824,3115815,7000690,65.000,63
2021,12,1529823,3115814,7000690,65.000,62
2021,1,1529822,3115867,7000691,83.000,67
2021,2,1530146,3103262,7000701,7368.000,7145
2021,3,1530145,3103261,7000701,10244.000,10133
2021,4,1530147,3103253,7000701,2390.000,2380
2021,5,1530148,3103253,7000701,2378.000,2145
2021,6,1530061,3111643,7000712,33456.000,31456
2021,7,1531185,3108532,7000712,13498.000,12566
2021,8,1530059,3107014,7000712,3296.000,3200
2021,9,1530060,3107014,7000712,6592.000,6389
2021,10,1530365,3107014,7000712,9882.000,9567
2021,11,1530366,3107014,7000712,9816.000,9800
2021,12,1530057,3106928,7000712,10182.000,10087
2022,1,1530058,3106928,7000712,1484.000,1300
2022,1,1531184,3106928,7000712,13168.000,12679
2022,1,1530332,3111639,7000713,18138.000,17000
];
left join(Factor_temp10)
load *, if (match(left(material,1),7),EN/MG) as faktor
Resident Factor_temp10;
I am greatful for any suggestions.
Thank you and best regards, Joanna
Hi all, I figured it out. Here's my solution:
Factor_temp10:
LOAD * INLINE
[
year,month,order,material,raw,EN,MG
2021,1,1530422,7114866,8000543,2060.000,1900
2021,2,1529821,7114866,8000543,2154.000,2003
2021,3,1530421,3008286,7000543,2060.000,1890
2021,4,1531020,3115651,7000640,3600.000,3200
2021,5,1530144,7114800,8000640,2970.000,2900
2021,6,1530215,7114800,8000640,3566.000,3500
2021,7,1530330,3114800,7000640,2010.000,1988
2021,8,1530331,3114800,7000640,3496.000,3456
2021,9,1531021,7005533,8000640,6624.000,6544
2021,10,1531022,7005533,8000640,1720.000,1700
2021,11,1529824,3115815,7000690,65.000,63
2021,12,1529823,3115814,7000690,65.000,62
2021,1,1529822,3115867,7000691,83.000,67
2021,2,1530146,3103262,7000701,7368.000,7145
2021,3,1530145,3103261,7000701,10244.000,10133
2021,4,1530147,3103253,7000701,2390.000,2380
2021,5,1530148,3103253,7000701,2378.000,2145
2021,6,1530061,3111643,7000712,33456.000,31456
2021,7,1531185,3108532,7000712,13498.000,12566
2021,8,1530059,3107014,7000712,3296.000,3200
2021,9,1530060,3107014,7000712,6592.000,6389
2021,10,1530365,3107014,7000712,9882.000,9567
2021,11,1530366,3107014,7000712,9816.000,9800
2021,12,1530057,3106928,7000712,10182.000,10087
2022,1,1530058,3106928,7000712,1484.000,1300
2022,1,1531184,3106928,7000712,13168.000,12679
2022,1,1530332,3111639,7000713,18138.000,17000
2022,1,1531100,3106928,8000712,13168.000,12679
2022,1,1530311,3111639,8000713,18138.000,17000
];
left join(Factor_temp10)
load *, if (match(left(material,1),7),EN/MG) as faktor
Resident Factor_temp10;
left join (Factor_temp10)
LOAD avg(faktor) as "avg faktor"
Resident Factor_temp10;
NoConcatenate
Factor_temp20:
load*
Resident Factor_temp10 where not match(left(material,1),7); Drop Table Factor_temp10;
NoConcatenate
Factor_temp30:
load*, if(match(left(raw,1),7),"avg faktor"*EN,EN) as Final
Resident Factor_temp20; Drop Table Factor_temp20;
BR, Joanna
Hi all, I figured it out. Here's my solution:
Factor_temp10:
LOAD * INLINE
[
year,month,order,material,raw,EN,MG
2021,1,1530422,7114866,8000543,2060.000,1900
2021,2,1529821,7114866,8000543,2154.000,2003
2021,3,1530421,3008286,7000543,2060.000,1890
2021,4,1531020,3115651,7000640,3600.000,3200
2021,5,1530144,7114800,8000640,2970.000,2900
2021,6,1530215,7114800,8000640,3566.000,3500
2021,7,1530330,3114800,7000640,2010.000,1988
2021,8,1530331,3114800,7000640,3496.000,3456
2021,9,1531021,7005533,8000640,6624.000,6544
2021,10,1531022,7005533,8000640,1720.000,1700
2021,11,1529824,3115815,7000690,65.000,63
2021,12,1529823,3115814,7000690,65.000,62
2021,1,1529822,3115867,7000691,83.000,67
2021,2,1530146,3103262,7000701,7368.000,7145
2021,3,1530145,3103261,7000701,10244.000,10133
2021,4,1530147,3103253,7000701,2390.000,2380
2021,5,1530148,3103253,7000701,2378.000,2145
2021,6,1530061,3111643,7000712,33456.000,31456
2021,7,1531185,3108532,7000712,13498.000,12566
2021,8,1530059,3107014,7000712,3296.000,3200
2021,9,1530060,3107014,7000712,6592.000,6389
2021,10,1530365,3107014,7000712,9882.000,9567
2021,11,1530366,3107014,7000712,9816.000,9800
2021,12,1530057,3106928,7000712,10182.000,10087
2022,1,1530058,3106928,7000712,1484.000,1300
2022,1,1531184,3106928,7000712,13168.000,12679
2022,1,1530332,3111639,7000713,18138.000,17000
2022,1,1531100,3106928,8000712,13168.000,12679
2022,1,1530311,3111639,8000713,18138.000,17000
];
left join(Factor_temp10)
load *, if (match(left(material,1),7),EN/MG) as faktor
Resident Factor_temp10;
left join (Factor_temp10)
LOAD avg(faktor) as "avg faktor"
Resident Factor_temp10;
NoConcatenate
Factor_temp20:
load*
Resident Factor_temp10 where not match(left(material,1),7); Drop Table Factor_temp10;
NoConcatenate
Factor_temp30:
load*, if(match(left(raw,1),7),"avg faktor"*EN,EN) as Final
Resident Factor_temp20; Drop Table Factor_temp20;
BR, Joanna