5 Replies Latest reply: May 19, 2015 7:34 AM by Stefan Wühl RSS

    SCD Type 1 error

    yusuuf rawat

      I have read all the discussion on SCD , they explain SCD are and some provide a  solution for Type 2 and other errors using interval match function , but not for type 1


      In my model i have a field called

      Application Name , the _date , and a server name.

      app1                         Aug-13               SRV100


      My dates are in Jan-15 format MMM-YY


      as time go's on the application name for that server changes from the incorrect names to the more  correct application names .

      How can i use the  application name used in the latest month for  all  other previous months

      I wish to keep my old data , creating an alternate column Latest_App name would suffice

      If the Application name in the latest month is blank or null , then i want to use the latest application name that is available


                              

      .                             

        • Re: SCD Type 1 error
          Jonathan Dienst

          SCD Type1 simply means that the mapping table is updated for all dates (no history).

          If i understand you correctly you would like to keep the old mapping and add the new

           

          For each of these, I would load a mapping table with the app names (one mapping table for old, one for new), and then use applymap to load both of them for all periods.

           

               ApplyMap('APPNAMES_OLD', [server name]) As ApplicationName,

               ApplyMap('APPNAMES_NEW', [server name]) As Latest_ApplicationName,

          • Re: SCD Type 1 error
            Stefan Wühl

            trim() removes leading or trailing blanks, len() returns the length of the field value.

             

            This combination of functions return zero when the argument is NULL or blank.

             

            The where clause will filter these records (exclude from being loaded into the mapping table).

            In your data example, you don't have cases in field APP that match, but your real data may have (that's what I assumed reading your requirement that you want to have the latest existing App name when the name in the latest month is NULL or missing).