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,
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:
Mapping LOAD SRV ,APP
order by the_date desc
See also attached.
test_2.qvw 139.5 K
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).