topic Re: Select a value based on another value in QlikView

Select a value based on another value

Fri, 24 Feb 2017 17:51:55 GMT

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?

Re: Select a value based on another value

avinashelite — Fri, 24 Feb 2017 18:21:00 GMT

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

Re: Select a value based on another value

Mon, 27 Feb 2017 14:27:18 GMT

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?

Re: Select a value based on another value

avinashelite — Mon, 27 Feb 2017 16:13:15 GMT

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

Re: Select a value based on another value

Tue, 28 Feb 2017 15:22:37 GMT

Could you send me a PM please?

Re: Select a value based on another value

Thu, 02 Mar 2017 12:20:40 GMT

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.

Re: Select a value based on another value

Wed, 08 Mar 2017 11:00:01 GMT

Anybody?

Re: Select a value based on another value

avinashelite — Wed, 08 Mar 2017 13:09:35 GMT

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

Re: Select a value based on another value

Thu, 09 Mar 2017 08:39:15 GMT

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?

Re: Select a value based on another value

avinashelite — Thu, 09 Mar 2017 09:50:40 GMT

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