Calculate a Field in script based on other calculated Fields

[Pestaninha]

Hi  - I need to Calculate a Field based on previous fields I calculated in the script

I basically need to calculate [Net Available Stock]  based on [Stock IN], [Stock] and [Demand requested ] Calculated previously in the script.

Please see code sample bellow. if someone could help would be great. Many thanks 

NoConcatenate
Metrics:
Load
SuperKeyM,
DATA,
QTY,
DateKey as Week,
[Weekly Avarage],
Period
Resident MasterData;

//// Calculate supply In ////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Supply In]
Resident Metrics
Where DATA='SI'
Group by SuperKeyM;

////Calculate Stock ////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Stock]
Resident Metrics
Where DATA='ST'
Group by SuperKeyM ;

//// Calculate Demand past////
Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Demand Requested1]

Resident Metrics
Where Period<>'Future' and DATA='TD'
Group by SuperKeyM;

//// Calculate Demand Future////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Demand Requestedcalc2]

Resident Metrics
Where Period='Future' and DATA='TD'
Group by SuperKeyM;

/// Calculate Compare Demand With Avarage////

Left Join(Metrics)
Load
SuperKeyM,
If([Demand Requestedcalc2]>[Weekly Avarage],[Demand Requestedcalc2],[Weekly Avarage]) as [Demand Requested2]
Resident Metrics
Where Period='Future';

///////////////////////////////////////Doesn't work from here //////////////////////////////////////////////////////////

/// Calculate Total Demand requested]

Left Join(Metrics)
Load
SuperKeyM,
[Demand Requested1] + [Demand Requested2] as [Demand requested]
Resident Metrics;

/////Calculate Net Available Stock /////

NoConcatenate
MetricsFinal:
Load
SuperKeyM,
[Supply In],
[Stock],
Week,
[Demand requested],
If(Week='W-13',(([Stock]+ [Supply In]) - ([Demand requested])), (((Peek([Net Available Stock])) +[Supply In])- ([Demand requested]))) as [Net Available Stock]
Resident Metrics;
Drop Table Metrics;

[sunny_talwar]

What issue are you running into when you run your script? Does it errors out?

[Pestaninha]

Hi Sunny - it does  load the fields [Demand requested] and [Net Available Stock]  but doesn't  return any value 

Thanks

[sunny_talwar]

Would you be able to provide sample data to see what might be the reason....

[Pestaninha]

Sample attached

[sunny_talwar]

Is this the output you are getting? or is this sample data? It seems that Net Available Stock is already a field which is why it might be the output I feel... also where is Week field? Is DateKey = Week?

[Pestaninha]

Hi Sunny, the first Table is a sample of the data, the second table is what i am getting with my script, also what i want to achieve but with correct values. Demand requested is returning all 0. If I Load fields Demand requested1 and Demand requested2  they return the correct values, but Demand Requested1 + Demand Requested1 = Demand requested  does not work

Yes, DateKey = Week

Many Thanks

[sunny_talwar]

sorry, I missed the tabs on the attached Excel.... checking it now

[sunny_talwar · Accepted solution]

Not 100% sure on what output you are looking to get, but does this look like what you want?

Script used

Metrics:
LOAD SuperKeyM, 
     DateKey as Week, 
     Period, 
     DATA, 
     QTY, 
     [Weekly Avarage]
FROM
[..\..\Downloads\Data Sample.xlsx]
(ooxml, embedded labels, table is Data);

//// Calculate supply In ////

Left Join(Metrics)
LOAD SuperKeyM,
	 Sum(QTY) as [Supply In]
Resident Metrics
Where DATA = 'SI'
Group by SuperKeyM;

////Calculate Stock ////

Left Join(Metrics)
LOAD SuperKeyM,
	 Sum(QTY) as [Stock]
Resident Metrics
Where DATA = 'ST'
Group by SuperKeyM;

//// Calculate Demand past////
Left Join(Metrics)
LOAD SuperKeyM,
	 Sum(QTY) as [Demand Requested1]
Resident Metrics
Where Period <> 'Future' and DATA = 'TD'
Group by SuperKeyM;

//// Calculate Demand Future////

Left Join(Metrics)
LOAD SuperKeyM,
	 Sum(QTY) as [Demand Requestedcalc2]
Resident Metrics
Where Period = 'Future' and DATA = 'TD'
Group by SuperKeyM;

///Calculate Compare Demand With Avarage, Calculate Total Demand requested, & Calculate Net Available Stock /////

tmpMetrics:
LOAD SuperKeyM,
	 [Supply In],
	 [Stock],
	 Week,
	 RangeSum([Demand Requested1], If([Demand Requestedcalc2]>[Weekly Avarage],[Demand Requestedcalc2],[Weekly Avarage])) as [Demand requested]
Resident Metrics;

MetricsFinal:
LOAD *,
	 If(Week = 'W-13', RangeSum([Stock], [Supply In], -[Demand requested]), RangeSum(Peek('Net Available Stock'), [Supply In], -[Demand requested])) as [Net Available Stock]
Resident tmpMetrics
Order By SuperKeyM, Week;

DROP Tables Metrics, tmpMetrics;

[Pestaninha]

Very much Sunny, thanks a million 🙂