Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

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.

1 Solution

Accepted Solutions
mongolu
Contributor

IF statement and set analysis problem

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

6 Replies
Not applicable

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

Not applicable

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.

Highlighted
mongolu
Contributor

IF statement and set analysis problem

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
Contributor

IF statement and set analysis problem

Sorry. Take out the two "NOT".

mongolu
Contributor

IF statement and set analysis problem

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

Not applicable

IF statement and set analysis problem

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