topic Re: Help with Resident Table in App Development

Help with Resident Table

acbishop2 — Fri, 25 May 2018 23:35:51 GMT

I am trying make a resident table out of a table whose data comes from another resident table, and I'm getting an error. Here's my simplified data structure:

[Dates]:

Load

[Pre-Processing],

[Processing],

[Underwriting],

[Closed]

From [file location];

[Speed]:

Load

NetWorkDays([Pre-Processing],[Processing]) AS [Pre-Processing Speed],

NetWorkDays([Processing],[Underwriting]) AS [Processing Speed],

NetWorkDays([Underwriting],[Closed]) AS [Underwriting Speed]

RESIDENT [Dates];

DROP TABLE [Dates];

[Refined Speed]:

Load

If([Pre-Processing Speed]<10,[Pre-Processing Speed],10) AS [Pre-Processing Speed],

If([Processing Speed]<10,[Processing Speed],10) AS [Processing Speed],

If([Underwriting Speed]<5,[Underwriting Speed],5) AS [Underwriting Speed]

RESIDENT [Speed];

DROP TABLE [Speed];

As far as I can tell, there shouldn't be an issue, but when I load the data, no "Speeds" show up at all. If I take away the [Refined Speed] table, it works just fine, but I need there to be a ceiling for the speeds (to get rid of outliers).

I appreciate any help in advance.

Re: Help with Resident Table

Neymar_Jr — Sat, 26 May 2018 05:31:58 GMT

Hi Aaron,

Try using the below script -

[Dates]:

Load

[Pre-Processing],

[Processing],

[Underwriting],

[Closed]

From [file location];

[Speed]:

Load

NetWorkDays([Pre-Processing],[Processing]) AS [Pre-Processing Speed],

NetWorkDays([Processing],[Underwriting]) AS [Processing Speed],

NetWorkDays([Underwriting],[Closed]) AS [Underwriting Speed]

RESIDENT [Dates];

DROP TABLE [Dates];

NoConcatenate

[Refined Speed]:

Load

If([Pre-Processing Speed]<10,[Pre-Processing Speed],10) AS [Pre-Processing Speed],

If([Processing Speed]<10,[Processing Speed],10) AS [Processing Speed],

If([Underwriting Speed]<5,[Underwriting Speed],5) AS [Underwriting Speed]

RESIDENT [Speed];

DROP TABLE [Speed];

Thanks,

Re: Help with Resident Table

marcus_sommer — Mon, 28 May 2018 08:13:14 GMT

Why using 3 loads when it could be easily done within a single-load like:

Load

rangemin(NetWorkDays([Pre-Processing],[Processing]),10) AS [Pre-Processing Speed],

rangemin(NetWorkDays([Processing],[Underwriting]),10) AS [Processing Speed],

rangemin(NetWorkDays([Underwriting],[Closed]),5) AS [Underwriting Speed]

From [file location];

and if there are more complex statements and you want a better readability like this you could use a Preceding Load.

- Marcus

Re: Help with Resident Table

acbishop2 — Tue, 29 May 2018 14:09:54 GMT

Unfortunately, my data is a little more complex and thus cannot be loaded by a single statement. However, the rangemin() function allowed me to combine the second and third tables in a way that gets rid of the error I was facing. Thanks!

Re: Help with Resident Table

acbishop2 — Tue, 29 May 2018 14:10:48 GMT

This gets rid of the error when I load the script, but it messes with the data and makes the average speeds go up. (They should go down.) Thank you for the help though!