Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a pivot table in which i have used the following if statement for an expression;
If(Left(GENERAL_LEDGER.GL_CODE,1)<>1 and Left(GENERAL_LEDGER.GL_CODE,2)<>21 and
Left(GENERAL_LEDGER.GL_CODE,2)<>28 and Left(GENERAL_LEDGER.GL_CODE,3)<>3 and
Left(GENERAL_LEDGER.GL_CODE,4)<>4 and Left(GENERAL_LEDGER.GL_CODE,5)<>5 and
Left(GENERAL_LEDGER.GL_CODE,6)<>6 and Left(GENERAL_LEDGER.GL_CODE,7)<>7 and
Left(GENERAL_LEDGER.GL_CODE,8)<>8 and Left(GENERAL_LEDGER.GL_CODE,9)<>9,
Sum({<TRANSACTION_HISTORY.VALUE_DATE= {"<$(=CALENDAR.VAR_DATE_ONE)"}>} TRANSACTION_HISTORY.CV_AMOUNT))
This expression is prvoing to be too big and slows down the .qvw operation.
I wanted to know if there is some way through which i could shorten it or maybe im doing it wrong and there is another way of getting the same result.
Thanks.
and replace the "AND" between the "MATCH" with "OR"
Put values 1, 3 -> 9, 21, 28 into an inline table and a flag =1 . Than the set expression (of IF as in your original code) has to check only one flag
-Alex
Thanks Alexandru. I pull data from a local server, that is connected to qlikview so it is not possible for me to perfrom any scripting or use macros, either.
msadrikhcb.
Your "Left"s functions could be written into only two:
1.
NOT Match( Left(GENERAL_LEDGER.GL_CODE,1), 1, 3, 4, 5, 6, 7, 8, 9)
2.
NOT Match ( Left(GENERAL_LEDGER.GL_CODE,2), 21, 28)
Your final function could then look like this:
Sum( {< TRANSACTION_HISTORY.VALUE_DATE = {"<$(=CALENDAR.VAR_DATE_ONE)"}
, GENERAL_LEDGER.GL_CODE -= {"= NOT Match( Left(GENERAL_LEDGER.GL_CODE,1), 1, 3, 4, 5, 6, 7, 8, 9)
and NOT Match ( Left(GENERAL_LEDGER.GL_CODE,2), 21, 28) "}
>}
TRANSACTION_HISTORY.CV_AMOUNT
)
Sorry. Take out the two "NOT".
and replace the "AND" between the "MATCH" with "OR"
Thanks Mihai, that did it, i only needed to change the AND's with OR.