topic Re: Slowly Changing Dimension in QlikView

Slowly Changing Dimension

Anonymous — Fri, 11 May 2012 08:50:02 GMT

I'm trying to figure out what to do for this SCD.

Any ideas would be greatly appreciated.

I've loaded my data below.

Slowly Changing Dimension

Anonymous — Fri, 11 May 2012 11:10:49 GMT

Only the salespeople change pharmacies and only 3 changes are made for this time perioud

Re: Slowly Changing Dimension

hic — Fri, 11 May 2012 11:40:49 GMT

You need to create a bridge table betweeen the sales people and the transaction table. See also attached qvw. I have added some changes in the end of your script, but you should probably try to incorporate them earlier in the script.

HIC

Re: Slowly Changing Dimension

Anonymous — Fri, 11 May 2012 12:04:00 GMT

thank you very much.
I will check it out now.

would it also work to do a load inline and use an Intervalmatch?

I have the load inline started in the model below

Re: Slowly Changing Dimension

Anonymous — Fri, 11 May 2012 12:20:46 GMT

I implemented your advice, but for some reason my Calendar isn't working in the script now

any ideas?

Re: Slowly Changing Dimension

hic — Fri, 11 May 2012 12:35:37 GMT

Inline table - no problems.

Also you should use an extended Intervalmatch for this inline table but this can be slightly tricky...

Step 1: Load the transaction table with the dates. In this table, create the field

Pharmacy & Date as PharmacyXDate

Step 2: Load the Interval table (your inline table) - SalesID, Start, End, Pharmacy. In this table, create the field

Pharmacy & Start & End as PharmacyXInterval

Step 3: Create the Intervalmatch table:

Intervalmatch (Date, Pharmacy) Load Start, End, Pharmacy resident Interval table

Step 4: From the Interval table, create a table with only the following two fields

Pharmacy & Date as PharmacyXDate

Pharmacy & Start & End as PharmacyXInterval

Step 5: From the interval table, create a table with the following fields

Pharmacy & Start & End as PharmacyXInterval

SalesID

Salesperson

Step 6: Drop the first interval table

But then it should work.

HIC

Re: Slowly Changing Dimension

hic — Fri, 11 May 2012 12:42:17 GMT

Yes, I added the following lines in your script:

Transactions2:

Load * ,

PharmacyName & '|' & Date(MonthStart(%_DateKey),'YYYYMM') as PharmacyXMonth

resident Transactions;

Drop Table Transactions;

So when you come to your master calendar, there is no longer a "Transactions" table and the Load statement will fail. The table has been renamed to "Transactions2". Just add the creation of the PharmacyXMonth into your Transcation table, and change references to "Transactions2" to "Transactions" - then you can delete the above lines.

HIC

Re: Slowly Changing Dimension

Anonymous — Fri, 11 May 2012 12:51:41 GMT

ahh of course, silly mistake for me

about the Load inline, as per your instructions in this step:

Step 2: Load the Interval table (your inline table) - SalesID, Start, End, Pharmacy. In this table, create the field

Pharmacy & Start & End as PharmacyXInterval

how do I create this field so that it works?

SalesLocationTmp:

LOAD * INLINE [

SalesID, SalesPerson, StartDate, EndDate, PharmacyName

1, Dennis, 201101, 201104, Arcade Pharmacy

3, Jack, 201105, 201109, Arcade Pharmacy

1, Dennis, 201101, 201109, Durbell Klinikem

1, Dennis, 201101, 201109, Essential Health Pharmacy Kuilsriver

2, Henry, 201101, 201109, Parow Family Pharmacy

2, Henry, 201101, 201109, Protea Heights Pharmacy

3, Jack, 201101, 201104, Jeffreys Bay Pharmacy

2, Henry, 201105, 201109, Jeffreys Bay Pharmacy

4, Kim, 201101, 201109, Klinicare Lorraine

4, Kim, 201101, 201105, Stellenbosch Square Pharmacy

5, Peter, 201106, 201109, Stellenbosch Square Pharmacy

5, Peter, 201101, 201109, Klinicare Bluewater Bay

5, Peter, 201101, 201109, Sun Valley Long beach

]

PharmacyName&'-'&StartDate&'-'&EndDate AS PharmacyXInterval,;

Thanks so much for your help, I really appreciate it

Re: Slowly Changing Dimension

hic — Fri, 11 May 2012 13:01:10 GMT

Just change the "Load * inline ... " to

"Load *, PharmacyName&'-'&StartDate&'-'&EndDate AS PharmacyXInterval inline ..."

HIC

Re: Slowly Changing Dimension

Anonymous — Fri, 11 May 2012 13:19:31 GMT

Intervalmatch (Date, PharmacyName) Load StartDate, EndDate, PharmacyName

resident SalesLocationTmp;

I'm getting a script error here. I think it is the "Date" field.
I tried with %_DateKey, but also didn't work

What do I need to use here? or is it something else?

Re: Slowly Changing Dimension

hic — Sat, 12 May 2012 13:50:36 GMT

You should use %_DateKey. But first you need to interpret the dates in the interval table as dates, e.g. through "Date#(StartDate,'YYYYMM') as StartDate". See attachment for a complete load statement.

HIC

Re: Slowly Changing Dimension

Anonymous — Sun, 13 May 2012 06:00:41 GMT

thanks, works great.

What about this? I don't know why it's not recognizing %_DateKey in this table now just below the interval match

PharmacyKeyTable:

LOAD

PharmacyName & '|' & %_DateKey as PharmacyXDate,

PharmacyName & '|' & StartDate & '|' & EndDate as PharmacyXInterval

resident SalesLocationTmp;