This time I have an issue with multiple reports in 1 request.
Here is the situation: I have requests which are linked to multiple reports (1 to many) These reports have an insertdate and a status.
Enclosed is an Excel sheet I made, explaining my problem more closely.
Situation explained in Excel:
There is 1 request = RQ1 wich is linked to 2 reports = R1 and R2 These have their own dates which is found in table START SITUATION I have joined the dates so that every report in the request has the same date this is found in table SITUATION NOW
What I would like to achieve is drawn out in the Excel sheet in the table = DESIRED RESULT
In my solution I have tried to achieve a result by using the interval_match function, this however hasn't helped me out. I have created a start_time and an end_time based on my insert_date_time field. And now I'm trying to join my new table with the one I have using the Interval_match function, but something is going wrong.
Can someone help me out, is the interval_match function I'm using wrong, or am I making a logical mistake.
In your example is the status VL (cell D27) retrieved from the previous status?
If you order by Request, Report and Date in Situation Now data set you can access the previous status through the previous() function (look for inter record functions in the help file). You would do this for all status fields having null.
For the data set without the nulls simply add an additional load excluding the nulls.
In the above replace null with empty field if your load does not create nulls.
I assume you already extend your "start situation" in the SQL query to provide all combinations of Request and Report with the Insert_Dates and Status.
In the second load you use ordering on Request, Report and Insert_date_time and - in case of equal Request and Report field value - add the previous status into the data set.
, if (previous(Request) = Request and previous(Report) = Report, previous(Status)) as Status
You will get null values for Status if either Request or Report is changing.
You can either stack loads or refer to already loaded tables. To get rid of the nulls simply add a load with a where clause to get rid of the unwanted records
This is my initial thought as well, I stepped off this solution, because the previous function doens't quite work here.
I've explained in excel what the problem with the if function is.
The if function runs through the dataset filling the records with the status of the previous records, but if you have two empy statusses in your dataset. The first will be filled up with the status of the previous but the second stays empty because in the dataset the previous is still empty.
I'm sorry for the complicated answer but I've tried to clearify this in the excel sheet.
Sorry that you had to point me to that repeatedly. Seen that it only works for single missing Statuses and I wasn't able to see a way around.
Assume that is why you started to look into interval matches.
Experimented also for a while to do create the extended table from your start table but QlikView started to have it's own ideas about the data and always came up with some Sync tables that ruined my tries.
Any change to add the logic into the data query side?
