topic Set Analysis sub query in QlikView

Set Analysis sub query

Wed, 09 May 2012 12:45:57 GMT

Hi,

I'm trying to solve a subquery case in the front end of QlikView.

I've a table with:

animal_ID

Lever_code

session_date

session_ID

Value

I have a straight table that displays this table but I want a extra field/expression that also give me the value of the max(session_date) where the session_date < session_date (of the record) and the Lever_code ='RR'.

Can someone solve this case in set analysis?

Thx for helping!

br,

Koen

Set Analysis sub query

SunilChauhan — Wed, 09 May 2012 12:54:41 GMT

may be you require below code

sum({<session_date={"<=$(Max(session_date))"}, Lever_code ={'RR'} >} Value)

hope this helps

Set Analysis sub query

Wed, 09 May 2012 13:17:25 GMT

thx for your reply but that's not the solution for my case.

the session date of the sub query should be the max (session date) of a set of data that gives me all the session dates that are smaller then the record session date and have a Lever_code = 'RR'

session_date < record_session_date and has Lever_code={'RR'} --> from this set of session_dates I need the value of the max(session_date)

I'm not sure it's possible with set analysis, maybe I need to load an extra island table (to compare the fields)

Re: Set Analysis sub query

jonathandienst — Wed, 09 May 2012 13:19:42 GMT

Koen

Not sure what you mean with the first condition. What are you comparing session_date with? Is session_date a dimension and you are comparing with that dimension?

If that is the case, I think you might need an island table to get the results you are after. I suggest that you post your model, or a sample.

Regards

Jonathan

Edit -- missed your second post

Set Analysis sub query

SunilChauhan — Wed, 09 May 2012 13:19:48 GMT

try this

only({<session_date={"<$((record_session_date ))"}, Lever_code ={'RR'} >} session_date)

hope this helps

Re: Set Analysis sub query

Wed, 09 May 2012 14:44:11 GMT

in attachment I've a test example . This example is working with an mirror island table of the main data. I just wonder I can create my extra expression in my straight table based on a sub query of the same data.

thx for helping!