Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm attempting to create a table of calculations. Specifically, these are slope calculations.
Every week, a report is generated that lists table data as such:
Key ForecastDate Forecast ReportDate
1 3/31/2016 3.79 3/1/2016
I can use a slope formula to discern slope for each key given min(ForecastDate) and max(ForecastDate)
Essentially,
vDeltaX = max(ForecastDate) - min(ForecastDate)
vDeltaY = sum({<ForecastDate = {"$(vMaxForecastDate)"}>}Forecast) - sum({<ForecastDate = {"$(vMinForecastDate)"}>}Forecast)
vSlope = $(vDeltaX) / $(vDeltaY)
I think that will work (assuming I can manage the syntax there).
So now for the load. I need to do that for every key and every report date.
I'm looking for something like:
Growth:
Load
Key,
ReportDate,
$(vSlope)
Resident MyTable;
Obviously a simplified version. Can someone help me expound on that?
Thanks a billion!
Something like this:
Temp1:
LOAD DISTINCT
Key,
ReportDate
FROM
...source...;
JOIN (Temp)
LOAD
Key,
ForecastDate,
Forecast
FROM
...source...;
Temp2:
LOAD
Key,
ForecastDate,
Forecast,
ReportDate
RESIDENT
Temp1
WHERE
ForecastDate <= ReportDate;
DROP TABLE Temp1;
Result:
LOAD
Key,
ReportDate,
(FirstSortedValue(Forecast, -ForecastDate) - FirstSortedValue(Forecast, ForecastDate)) / (Max(ForecastDate) - Min(ForecastDate)) as Slope
RESIDENT
Temp2
GROUP BY
Key, ReportDate;
DROP TABLE Temp2;
Thanks gysbert,
Ok, after a number of iterations, here's my current code - which does produce a 'slope' table, though with very few entries, and all of them blank in the 'slope' field.
The questions I have here are:
a) Where ForecastDate <= ReportDate - I don't think I would ever expect the forecast date to be less than the report date - forecast implies future. I'm not quite understanding this one.
b) (FirstSortedValue(ForecastUtilization, -ForecastDate) - what's the preceding dash for in front of ForecastDate? If I leave it or remove it, doesn't seem to make an impact.
I removed the (Growth)Temp2 table as it seemed redundant, and with it in, the Slope table errored ever time. You'll notice I also added:
PortKey as SlopeKey, to eliminate synthetic keys.
Thoughts?
Thank you!
-----------------------------------
GrowthTemp1:
LOAD DISTINCT
PortKey,
ReportDate
Resident ForecastData;
JOIN (GrowthTemp1)
LOAD
PortKey,
ForecastDate,
ForecastUtilization
Resident ForecastData
WHERE
ForecastDate <= ReportDate;
// GrowthTemp2:
// LOAD
// PortKey,
// ForecastDate,
// ForecastUtilization,
// ReportDate
// RESIDENT
// GrowthTemp1
// WHERE
// ForecastDate <= ReportDate;
// DROP TABLE GrowthTemp1;
Slope:
LOAD
PortKey as SlopeKey,
ReportDate,
(FirstSortedValue(ForecastUtilization, -ForecastDate) - FirstSortedValue(ForecastUtilization, ForecastDate)) / (Max(Date(ForecastDate)) - Min(Date(ForecastDate))) as Slope
RESIDENT
GrowthTemp1
GROUP BY
PortKey, ReportDate;
DROP TABLE GrowthTemp1;
a) Where ForecastDate <= ReportDate - I don't think I would ever expect the forecast date to be less than the report date - forecast implies future. I'm not quite understanding this one.
Hmm, yeah, that's should probably be >=. But with only one line of example data I really can't tell.
Ok, So since my data was already all loaded, there was no need for the temp tables.
Here's what I have currently - which does, in fact, produce the table and intended results.
Slope:
LOAD
PortKey as SlopeKey,
ReportDate,
(FirstSortedValue(ForecastUtilization, -Date(ForecastDate)) - FirstSortedValue(ForecastUtilization, Date(ForecastDate))) / (Max(Date(ForecastDate)) - Min(Date(ForecastDate))) as Slope
RESIDENT
ForecastData
GROUP BY
PortKey, ReportDate;
So currently, the problem is - when I try to show these values in a straight table, or bar chart (with our without aggregation functions), the processing time generally times out and it completely overwhelms the application. I would have assumed that it did the calcs during the load (as the values are in the table), so theoretically displaying them shouldn't be an issue I would have thought. Apparently I was wrong. It will not generate a straight table with these 3 dimensions (from the Slope table) in it.
EDIT:
I think I found the problem. I was able to get a table with PortKey and Slope with just one PortKey selected. There are 38,777 values for that one PortKey /Port listed in the table, but just one per SlopeKey. A SlopeKey should relate 1:1 to Port/PortKey for the Port will be the primary dimension. Hmmmmmmmmmmm
If I remove the PortKey AS SlopeKey it creates a synthetic key.
Thoughts?