Creating a specific factor and use it for further calculations

[JoannaM]

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

[JoannaM · Accepted solution]

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