topic Re: Set Analysis Complex And/Or in QlikView

Set Analysis Complex And/Or

Tue, 07 Jan 2014 15:51:28 GMT

I have the below set analysis. my basic question is, how do you specify the groups of a complex and/or clause?

sum({<TRD_DATE={"$(='>=' & Start_Date & '<=' & End_Date)"}>

*<CANCEL_EFF_DATE={"$(='>' & End_Date)"}>

+<BASE_BOOK_VALUE={"=Len(Trim(CANCEL_EFF_DATE))=0"}>}

BASE_BOOK_VALUE)

For example:

A and B or C

(A and B) or C

A and (B or C)

How do you apply the parentheses? In my sample above, I assume this is running (A and B) or C because of order of operations, just going left to right. I need to ensure that it does A and (B or C).

Re: Set Analysis Complex And/Or

Tue, 07 Jan 2014 16:07:20 GMT

I assume a parenthesis around an and function would be the same as:

<A,B>+<C>

But i'm not sure how to do an or inside parenthesis.

Re: Set Analysis Complex And/Or

Nicole-Smith — Tue, 07 Jan 2014 16:12:25 GMT

Try:

sum({<TRD_DATE={"$(='>=' & Start_Date & '<=' & End_Date)"}, CANCEL_EFF_DATE={"$(='>' & End_Date)"}>

+<BASE_BOOK_VALUE={"=Len(Trim(CANCEL_EFF_DATE))=0"}>}

BASE_BOOK_VALUE)

Re: Set Analysis Complex And/Or

Tue, 07 Jan 2014 20:55:15 GMT

That puts the parenthesis around the and function, not the or, right? or does it end up being (A or C) and (B or C)?

Re: Set Analysis Complex And/Or

Nicole-Smith — Tue, 07 Jan 2014 20:56:03 GMT

Correct

Re: Set Analysis Complex And/Or

Tue, 07 Jan 2014 20:56:35 GMT

i need the parenthesis around the or function though, that's my problem.

Re: Set Analysis Complex And/Or

Tue, 07 Jan 2014 21:00:33 GMT

To step out of academia, I have a very complicated SQL where clause that I need to replicate in set analysis, which melds and/or and lots of parenthesis. there might be some additional parenthesis at the end, please ignore:

( ( ( T.TRD_DATE between $(vBegin) and $(vEnd) )

and ( t.effective_date <= $(vEnd) )

and ( t.cancel_eff_date is null

or t.cancel_eff_date > $(vEnd) ) )

or ( ( T.TRD_DATE <= $(vBegin) )

and ( ( t.effective_date between $(vBegin) and $(vEnd)

and ( t.cancel_eff_date is null

or t.cancel_eff_date > $(vEnd) ) )

or ( ( t.effective_date < $(vBegin) )

and ( t.cancel_eff_date between $(vBegin) and $(vEnd) ) ) ) ) );

Re: Set Analysis Complex And/Or

Nicole-Smith — Tue, 07 Jan 2014 21:03:18 GMT

I think if you reorder it to this, it might give you what you want (but I can't say for sure without having your .qvw and data):

sum({<BASE_BOOK_VALUE={"=Len(Trim(CANCEL_EFF_DATE))=0"}>+

<CANCEL_EFF_DATE={"$(='>' & End_Date)"}>*

<TRD_DATE={"$(='>=' & Start_Date & '<=' & End_Date)"}>}

BASE_BOOK_VALUE)