I have 2 fields I created in the script from the below post... I used those fields to do further calculation in the app to get the scores I needed, but now I'm unable to add them together because I need the scores I'm calculating in the app to be done in the script as well. I think I need a preliminary load, but can't find the right spot to put the calculation in the script without getting an error.
The above post generates DTP Score and CO2 DTP Score (there are other scores I'm generating in the script as well)... I calculate [Total LXN DTP Score] And [Total CO2 DTP Score] in the app using these calcuations:
[Total CO2 DTP Score]=IF([CO2 Score]=0,0,IF([CO2 Score]>0 And [Product Score]>[CO2 Score],0,IF([CO2 DTP Score]=0,-500,[CO2 DTP Score])))
[Total LXN DTP Score]=IF([DTP Score]>0,[DTP Score],IF([Nitrogen Score]>0 OR [Oxygen Score]>0 AND [DTP Score]=0,-500,IF([DTP Score]=0,0)))
How would I insert these calculations in my script to generate the total scores with the script instead of a new field in the app so I can add them together in the end? Below is a subset of the relevant script.
Scores:
LOAD
"Business Partner" AS [BPARTNER],
"Characteristic Name",
Category,
"Characteristic Name Desc",
"Counter for Multiple-Value Characteristics",
"Characteristic Value",
"Characteristic Value Desc",
"Internal Value from",
"Internal Value to",
"No of Mkt Attributes"
FROM [lib://CRM/30_QVD_Extract/SAP_BI_HANA_ZATR_DS1.QVD]
(qvd);
Result:
Load
[BPARTNER],
sum([Internal Value from]) as [Total Internal Value From]
Resident Scores
Where
[Characteristic Name Desc] = 'AP Closest Plant Miles'
Group By
[BPARTNER];
[LXN DTP Score]:
LOAD * INLINE [
Start, End, DTP Score
0, 50, 50
51, 150, 30
151, 200, 10
201, 999, 0
];
Inner Join IntervalMatch ( [Total Internal Value From] )
LOAD Start, End
Resident [LXN DTP Score];
Left join (Result)
Load
[Total Internal Value From],
[DTP Score]
Resident [LXN DTP Score];
Drop Table [LXN DTP Score];
Result2:
Load
[BPARTNER],
sum([Internal Value from]) as [Total Internal Value From]
Resident Scores
Where
[Characteristic Name Desc] = 'AP Closest CO2 Plant Miles'
Group By
[BPARTNER];
[CO2 DTP Score]:
LOAD * INLINE [
Start, End, CO2 DTP Score
0, 50, 50
51, 150, 30
151, 200, 10
201, 999, 0
];
Inner Join IntervalMatch ( [Total Internal Value From] )
LOAD Start, End
Resident [CO2 DTP Score];
Left join (Result2)
Load
[Total Internal Value From],
[CO2 DTP Score]
Resident [CO2 DTP Score];
Drop table [CO2 DTP Score];
Left join (Result)
Load
[BPARTNER],
[CO2 DTP Score]
Resident Result2;
drop table Result2;
Load
[BPARTNER],
SUM(IF([Characteristic Name Desc]='Key Account' And [Characteristic Value]='1010',1,0)) AS [Key Account],
SUM(IF([Characteristic Name Desc]='Focused Account' And [Characteristic Value]='1010',1,0)) AS [Focused Account],
SUM(IF([Characteristic Name Desc]='Ar-Argon' And [Characteristic Value]='1010',15)) AS [Argon Score],
SUM(IF([Characteristic Name Desc]='CO2-Carbon Dioxide' And [Characteristic Value]='1010',10)) AS [CO2 Score],
SUM(IF([Characteristic Name Desc]='H2-Hydrogen' And [Characteristic Value]='1010',20)) AS [Hydrogen Score],
SUM(IF([Characteristic Name Desc]='He-Helium' And [Characteristic Value]='1010',15)) AS [Helium Score],
SUM(IF([Characteristic Name Desc]='O2-Oxygen' And [Characteristic Value]='1010',5)) AS [Oxygen Score],
SUM(IF([Characteristic Name Desc]='N2-Nitrogen' And [Characteristic Value]='1010',10)) AS [Nitrogen Score]
Resident
Scores
GROUP BY [BPARTNER];
