Slowly Changing Dimension

[Anonymous]

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

Any ideas would be greatly appreciated.

I've loaded my data below.

[Anonymous]

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

[hic]

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

[Anonymous]

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

[Anonymous]

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

any ideas?

[hic]

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

[hic]

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

[Anonymous]

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

[hic]

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

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

HIC

[Anonymous]

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?