IF statement and set analysis problem

[#1]

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.

[#2]

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

[#3]

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.

[mongolu]

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
 )

[mongolu]

Sorry. Take out the two "NOT".

[mongolu · Accepted solution]

and replace the "AND" between the "MATCH" with "OR"

[#7]

Thanks Mihai, that did it, i only needed to change the AND's with OR.