topic IF statement and set analysis problem in QlikView

IF statement and set analysis problem

Mon, 13 Dec 2010 06:27:35 GMT

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

Mon, 13 Dec 2010 09:04:01 GMT

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

Mon, 13 Dec 2010 12:55:14 GMT

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

mongolu — Mon, 13 Dec 2010 18:43:00 GMT

msadrikhcb.

Your "Left"s functions could be written into only two:

NOT Match( Left(GENERAL_LEDGER.GL_CODE,1), 1, 3, 4, 5, 6, 7, 8, 9)

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

mongolu — Tue, 14 Dec 2010 04:53:53 GMT

Sorry. Take out the two "NOT".

IF statement and set analysis problem

mongolu — Tue, 14 Dec 2010 04:58:47 GMT

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

IF statement and set analysis problem

Wed, 29 Dec 2010 09:20:05 GMT

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