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' on24 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.
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:
,'Donation' AS FactType
,'Donor change' AS FactType
,'Results' AS FactType
,'Appointment' AS FactType
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.:
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?
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: