# IF statement and set analysis problem

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.

• ###### IF statement and set analysis problem

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

• ###### IF statement and set analysis problem

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.

• ###### IF statement and set analysis problem

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 )`

• ###### IF statement and set analysis problem

Sorry. Take out the two "NOT".

• ###### IF statement and set analysis problem

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

• ###### IF statement and set analysis problem

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