4 Replies Latest reply: May 29, 2018 10:10 AM by Aaron Bishop RSS

    Help with Resident Table

    Aaron Bishop

      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
          Rajesh Thakur

          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

          • Re: Help with Resident Table
            Marcus Sommer

            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