Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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.
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)
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)?
Correct
i need the parenthesis around the or function though, that's my problem.
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) ) ) ) ) );
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)