topic Re: Table of calculations in App Development

Table of calculations

joey_lutes — Thu, 01 Sep 2016 23:16:53 GMT

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!

Re: Table of calculations

Gysbert_Wassenaar — Fri, 02 Sep 2016 06:04:47 GMT

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;

Re: Table of calculations

joey_lutes — Tue, 06 Sep 2016 18:16:18 GMT

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;

Re: Table of calculations

Gysbert_Wassenaar — Wed, 07 Sep 2016 06:02:36 GMT

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.

Re: Table of calculations

joey_lutes — Fri, 09 Sep 2016 20:01:43 GMT

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?