Table of calculations

[joey_lutes]

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!

[Gysbert_Wassenaar]

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;

[joey_lutes]

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;

[Gysbert_Wassenaar]

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.

[joey_lutes]

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?