Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. 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
Highlighted
mongolu
Contributor

IF statement and set analysis problem

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

View solution in original post

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.

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".

Highlighted
mongolu
Contributor

IF statement and set analysis problem

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

View solution in original post

Not applicable

IF statement and set analysis problem

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