topic Re: SCD Type 1 error in QlikView

SCD Type 1 error

vvvvvvizard — Tue, 19 May 2015 09:00:05 GMT

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

jonathandienst — Tue, 19 May 2015 09:16:28 GMT

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

vvvvvvizard — Tue, 19 May 2015 10:04:20 GMT

I tried it in this test model with test data .

Couldn't get it to work

Re: SCD Type 1 error

swuehl — Tue, 19 May 2015 11:11:19 GMT

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

;

Re: SCD Type 1 error

vvvvvvizard — Tue, 19 May 2015 11:19:36 GMT

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

Re: SCD Type 1 error

swuehl — Tue, 19 May 2015 11:34:24 GMT

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