Hello,
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.
Solved: Need to score miles from a plant in ranges... as a... - Qlik Community - 1792939
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];
