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.:
Dimension = Date
Expression = Only(Quarantine_Status)
This will give you what you're asking for above.
Hope this helps,
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,
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.
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?
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!
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:
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
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.
Hope this helps,