Select a value based on another value

[#1]

Hi All,

I'm struggling with how to implement something, even from a conceptual point of view, in Qlikview. I've got 2 tables:

CM  (this is a basic "item")

     ID  (primary key)

     DESC (textual description)

     ...

CME (this is a history log of the item it links to)

     ID (primary key)

     CM_ID (FK to CM.ID)

     DATE (linked to a master calender)

     STATUS

The CME table is effectively the "history table" of the CM entities. The issue I have is that I want my report to be periodic. So given particular date selections that the report has (which are all linked to a master calendar) I want to be able to say what the STATUS was of the CM item at the given point in time.

Just to complicate things, CMEs are not created daily, so the status of a CM at a selected time period is defined as the status of the CME record with the highest date just within the selected date range. So if someone selects Q4 2016 within the report, and a particular CM has 2 CMEs in Q4 with dates 10/12/2016 and 15/12/2016, it would be the status of the CME record with the 15th of Dec datestamp that I'd need to be returned.

I need this status to be easily accessible from various charts and tables too, almost like the status is part of the actual CM record, since it's a common thing to show against a CM (or use as a dimension in charts).

Any ideas?

[avinashelite]

you could use the max(DATE)...

Try like this

only({<DATE={"=max(DATE)"}>}STATUS)

this will give the Status with the Max date in the quarter

max(DATE)..this will give the DATE

how you are defining the range using the variables ? then we need to include the range values in the the set analysis

[#3]

Hi Avinash,

Many thanks for your suggestion. However, I'm not sure it's working as intended.

I tested it using a straight table, with columns from the CM table and the status; but where there were multiple CME records in the quarter (so multiple statuses), the script above seemed to be returning multiple values and so the column just showed "-". I'm just guessing that is the problem.

Or could this be because the max date in the quarter isn't the same as the max date of the CME record?

[avinashelite]

Need to check the data model ...share the app let me check

[#5]

Could you send me a PM please?

[#6]

Sorry for the delay, I had to remove a bunch of sensitive things from the app.

Please find the app here. I've annotated some unexpected behaviour with some text boxes within it.

[#7]

Anybody?

[avinashelite · Accepted solution]

check the doc I have updated the logic in your file ...

[#9]

Hi Avinash,

Thanks so much. I will go and test it today to make sure it works in all conditions, but initially it's looking great.

I see what you did (resolve a variable to the max date rather than put it in the script), but I'm not sure why that works over just having it inside the script. What is the difference?

[avinashelite]

Implementation would be much cleaner and you can use the same variable in many chart ..other than that it does not have any impact