SCD Type 1 error

[vvvvvvizard]

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

.                             

[jonathandienst]

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,

[vvvvvvizard]

I tried it in this test model with test data .

Couldn't get it to work

[swuehl · Accepted solution]

The key to this approach is that your mapping table needs to be build correctly, i.e. map latest app names to the key server name.

Since the mapping table will take the first existing value for your key, you need to order your mapping table by date desc (and maybe filter out the NULLs).

For example using:

MAP:

Mapping LOAD SRV ,APP

Resident A

where len(trim(APP))

order by the_date desc

;

See also attached.

[vvvvvvizard]

It works , just trying to understand how where len(trim(APP)) removes the nulls ?

[swuehl]

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).