Help with Resident Table

[acbishop2]

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.

[Neymar_Jr]

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,

RT

[marcus_sommer · Accepted solution]

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

[acbishop2]

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!

[acbishop2]

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!