As of Status

[shariqusmani]

Hello,

I'm fairly new at QlikView and couldn't find a solution by searching, mainly because I didn't know how to word my search.

I have a table with claim numbers and their status at various points in time. The table does not have a status for every possible date. All dates are month end dates. See below:

ClaimNo  Status       StatusDate

           1   Open        2011-12-31

           1   Open        2012-04-30

           1   Closed      2013-06-30

           1   Reopen    2015-07-31

In a chart, I want a user to be able to know the status of a claim at whatever date they select. The date is entered into an input box and saved in a variable vSelectedDate.

If the vSelectedDate = StatusDate, then there is no problem, it will return the status at that date for that claim, but he issue I have is, if the user selects a date of 2014-12-31, I want to show the status of Closed because there was no change in status between 2013-06-30 and 2014-12-31, but the table does not have this status for that date.

Is there some way I can get the status for dates not in the table based on the last known date and status?

I am open to a solution either on the front end or in the load script, whichever is easier.

Any help is greatly appreciated. Thank you in advance.

Shariq.

[sunny_talwar]

I think you would need to implement IntervalMatch in the script to be able to achieve what you are looking for. Try this link to see if you can understand what it is -> IntervalMatch

I think once you implement that, you will be able to use the date from mastercalendar to query the status for any specific date.

[swuehl]

Maybe like

=FirstSortedValue({<[StatusDate] = {"<=$(vSelectedDate)"} >} [Status], -[StatusDate])

You need to take care that variable vSelectedDate format matches format of StatusDate.