Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Not applicable

Select a value based on another value

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?

1 Solution

Accepted Solutions
Highlighted

Re: Select a value based on another value

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

View solution in original post

9 Replies
Highlighted

Re: Select a value based on another value

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



Highlighted
Not applicable

Re: Select a value based on another value

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?

Highlighted

Re: Select a value based on another value

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

Highlighted
Not applicable

Re: Select a value based on another value

Could you send me a PM please?

Highlighted
Not applicable

Re: Select a value based on another value

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.

Highlighted
Not applicable

Re: Select a value based on another value

Anybody?

Highlighted

Re: Select a value based on another value

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

View solution in original post

Highlighted
Not applicable

Re: Select a value based on another value

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?

Highlighted

Re: Select a value based on another value

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