I am working on a qlik project that is already in production. We have date ranges that we compute the days between to determine
how long the item stayed in a particular status. Now, we are learning that those dates from the main table are just the latest dates they were in the status and an item can be in 1 status several times. Users want to bring in the history table where we can get all the dates they went in and out of a status.
Here's a sample:
MainTable:
RecID,InReviewDate,OutReviewDate
1, 10/01/2015, 10/06/2015 // this would be 5
2, 09/18/2015, 09/20/2015 // this would be 2
3, 10/23/2015, 10/30/2015 // this would be 7
History table :
RecID,Field,Value
1, InReviewDate,09/13/2015
1, OutReviewDate,09/17/2015
1, InReviewDate,09/20/2015
1, OutReviewDate,09/22/2015
1, InReviewDate,10/01/2015
1, OutReviewDate,10/06/2015
2, InReviewDate,09/18/2015
2, OutReviewDate,09/20/2015
3, InReviewDate,10/05/2015
3, OutReviewDate,10/10/2015
3, InReviewDate,10/23/2015
3, OutReviewDate,10/30/2015
#of days they are in-review using the history should be :
1=11
2=2
3=12
I only want to bring the cumulative # of days into the MainTable, what would be the easiest and most efficient way to do this?
Thank you. That is brilliant. After further analysis of the data though, our requirement has changed. I learned that in the front-end, they can change the value of InReview or OutReview anytime - that will create an OldValue and NewValue in the history table. If they changed the value, we need to get the NewValue with the latest EntryDate. If there is no OutReviewDate, we should subtract the InReviewDate with the current date.
for the above example, here would be the desired results.
RecID = 1
9/13 to 9/17 = 4 //1st record is disregarded because the 2nd record is a correction entry
9/20 to 9/22 = 2
total = 6
RecID = 2
9/18 to 9/20 = 2
total = 2
RecID = 3
10/05 to 10/10 = 5
10/23 to today(11/15) = 23 // disregard 2nd to last entry because this has been corrected on the last record
//Since there is no OutReviewDate entry after the last entry date, use the current date(11/15)
Total = 28
Since the source is history, we know that the table would get bigger in time, is there any way we can get the computation in the script instead of expression in the application?
Since the source is history, we know that the table would get bigger in time, is there any way we can get the computation in the script instead of expression in the application?
Not if users change values in the front-end. Users cannot interact with the script and the script can't interact with users or use chart expressions or values that users change in input fields or variables.
