topic Re: Slowly changing dimension in QlikView

Slowly changing dimension

Tue, 17 Jul 2012 07:35:35 GMT

My goal is to be able to study how some fields have changed over the last year(for example).

I have two tables, DONOR and DONATION that are connected to each other via the key DONOR_ID.

In the table DONOR I have a field called 'QUARANTINE_STATUS' and in the field DONATION I have a field called 'DONATION_RESULT'.

Lets say that the field 'QUARANTINE_STATUS' connected to a certain donor changed from 'OK' to 'PENDING_RESULTS' on 24 July 2011. I want to be able to select a month and a year and then see a snapshot for the chosen time period. If i choose March 2011 then 'QUARANTINE_STATUS' for this donor would be 'OK' but if i choose September 2011 then it would show 'PENDING_RESULTS'. The same applies for 'DONATION_RESULT' wich I also want to study in the same way.

In both tables there is a timestamp that updates every time something in that table changed.

Please help me write this script, since I am new to QlikView I have no idea on how to do this.

Thank you

Databyran

Re: Slowly changing dimension

Jason_Michaelides — Tue, 17 Jul 2012 08:32:11 GMT

You should concatenate all tables that have a datestamp (i.e. can be considered transactions of a sort) into one table. Then you will only have one date field which you can link to a master calendar of all dates and associated field (Month, Year, Week, Quarter etc). Separate the different Fact Types with a new field:

Fact:

LOAD

,'Donation' AS FactType

FROM...Donations...;

CONCATENATE (Fact)

LOAD

DonorFieldsThatChangeOverTime

,'Donor change' AS FactType

FROM....Donors...;

CONCATENATE (Fact)

LOAD

,'Results' AS FactType

FROM...Results...;

CONCATENATE (Fact)

LOAD

,'Appointment' AS FactType

FROM...Appointment...;

And so on...

Then, in the UI you can use Set Analysis if necessary to calculate whatever you want and all within the same date selections. Sometimes it won't be necessary E.g.:

Dimension = Date

Expression = Only(Quarantine_Status)

This will give you what you're asking for above.

Hope this helps,

Jason

Re: Slowly changing dimension

swuehl — Tue, 17 Jul 2012 09:05:57 GMT

Jason,

I think databyron's problem is a bit more complicated, since he only have records for the points in time the status changed, not for all dates he is interested in.

To handle this, there are some possible solutions, one standard would be to use the extended syntax of INTERVALMATCH LOAD prefix.

databyron, you first need to create a validfrom and validto timestamps / dates from your one change status date field.

You can do this by ordering your table by DonorID and Date desc and then use peek() function to retrieve the previous value.

Then you can link your time intervals to a master calendar using INTERVALMATCH.

There are some examples here in the forum on how to do that (often in a samples related to currency exchange issues).

There are also some examples on how to do something very similar not using INTERVALMATCH, so you might want to investigate into this direction to.

There are even some possible solutions using only expressions in the frontend, but I personally would try to solve your problem in the script first.

Hope this helps,

Stefan

Re: Slowly changing dimension

Jason_Michaelides — Tue, 17 Jul 2012 09:13:25 GMT

Reading through it again you're absolutely right, Stefan. To get a snapshot for any particular date IntervalMatch() would be necessary. Thanks for the correction!

Databyron, the rest of my principal is still true. Use IntervalMatch() to build your DonorChange temptable then concatenate it to the main Fact table.

Cheers,

Jason

Re: Slowly changing dimension

Tue, 17 Jul 2012 10:55:15 GMT

Thank you Stefan,

How should I create my validfrom and validto timestamps?

I have another timestamp in the DONOR table called CREATE_TIMESTAMP that I could use the first time I load the data as a validfrom timestamp. But the next time I load and detect a change for a particular donor, I first want to set the validto for the old data to the current CHANGE_TIMSTAMP. The new data gets validfrom = CHANGE_TIMSTAMP.

My problem is that when a field changes in my sql-database the old data is overwritten so I need to keep track of changes in the qlikview-database and add a line there. Am I wrong when i say that I need to use both qwd-files and the intervalmatch function?

I am a little troubled wheter QlikView is a good tool to solve this problem?

Regards

Databyran

Re: Slowly changing dimension

Jason_Michaelides — Tue, 17 Jul 2012 11:06:53 GMT

No, you're not wrong. Look up incremental loads in this forum and use of IntervalMatch() to get your answers.

Jason

Re: Slowly changing dimension

Tue, 17 Jul 2012 11:47:59 GMT

Thank you Jason.

I have already checked incremental load and a managed to perform incremental load but the problem is that I dont see how I should use intervalmatch togheter with the incremental load.

I am unsure how to add a line to the qvd-file instead of just overwriting the old data.

can you please explain how to:

store just the table DONOR in an qvd and check if QUARANTINE_STATUS changed, if QUARANTINE_STATUS changed I want to add a line to this DONOR_ID and then update the qvd.

I am very thankful for your help!

Databyran

Re: Slowly changing dimension

swuehl — Tue, 17 Jul 2012 12:29:31 GMT

databyron,

I think there are some different topics you need to handle and we should try not mixing them up, some of the topics are:

1) Link dates from multiple tables to a common master calendar

As Jason said you can concatenate the tables, creating one date field and a type field and link this single date field to the master calendar. Using a linkage table is a quite similar approach, so I would search the forum for linkage table, master calendar, multiple date fields.

2) If you need to handle matching dates (from the master calendar) to time intervals (from the fact table, instead of dates), you might want to look into IntervalMatch.

This might help you in creating a start and end date field (status valid from / valid to) from your one date field:

http://community.qlik.com/message/169873#169873

As said, there are a lot of examples here in the forum covering INTERVALMATCH, search for INTERVALMATCH or currency exchange rate etc. Then you find something like

http://community.qlik.com/message/93746#93746

3) Incremental load

I suggest that you have a look into the nice example in Rob Wunderlich's QV cookboook, the steps to reproduce are quite well explained.

http://robwunderlich.com/downloads/

Hope this helps,

Stefan