topic Re: Add condition in QlikView

Add condition

imtiazgirach — Wed, 25 Nov 2020 16:16:04 GMT

Hi...I'd like to add a condition to a straight table report to exclude data for certain types of journal category but I am not sure how to do this in the expression.

The exclusion condition I need is

(GLSubLedg.JournalCategory) <> 'Budget' AND (GLSubLedg.JournalCategory) <> 'Payments'

I would really appreciate some assistance with this. Thanks

Re: Add condition

Chanty4u — Wed, 20 Jun 2018 15:56:08 GMT

may be this

sum({<GLSubLedg.JournalCategory-={'Budget',' Payments'}>}Sales)

sum({<GLSubLedg.JournalCategory/={'Budget',' Payments'}>}Sales)

Re: Add condition

trdandamudi — Wed, 20 Jun 2018 15:56:45 GMT

Say if you have a field amount you can use the below expression:

Sum({<GLSubLedg.JournalCategory-={'Budget','Payments'}>} Amount)

Re: Add condition

imtiazgirach — Wed, 20 Jun 2018 18:19:51 GMT

Thanks. I don't have an amount field on its own but I do have amount cr and amount dr fields so I can add a calculated fields amount cr - amount dr to get an amount field of that works as in some fields there might be amount cr and in others just amount dr. Not sure if that would make a difference in the expression?

Re: Add condition

trdandamudi — Wed, 20 Jun 2018 20:19:07 GMT

You can try as below:

Sum({<GLSubLedg.JournalCategory-={'Budget','Payments'}>} amount cr - amount dr)

Re: Add condition

imtiazgirach — Wed, 20 Jun 2018 22:58:29 GMT

Thanks. I will try this and let you know.

To help me understand for future requirements if I needed to change the condition to pick up data for where the journal category is say Sales so rather than exclude, the condition would be journal category = 'Sales' how would I need to change the condition.

Re: Add condition

Chanty4u — Thu, 21 Jun 2018 06:17:53 GMT

may be this

sum({<GLSubLedg.JournalCategory={'Sales'}>} amount cr - amount dr)

Re: Add condition

imtiazgirach — Thu, 21 Jun 2018 07:37:26 GMT

I am getting errors in expression in all the above. I am using Qlikview version 11 not sure if that makes a difference. Thanks

Re: Add condition

imtiazgirach — Thu, 21 Jun 2018 14:55:36 GMT

I slightly amended the expression and now the expression is ok but the report is still showing lines with the 'Budget' journal category. I don't want these lines on the report

This is the amended expression:

Sum({<GLSubLedg.JournalCategory-={'Budget'}>} GLSubLedg.AmountCR-GLSubLedg.AmountDR)

Even with this expression the budget category lines are showing on the report:

sum({<GLSubLedg.JournalCategory=['Permanent','Reversing']>}GLSubLedg.AmountCR-GLSubLedg.AmountDR)

Re: Add condition

trdandamudi — Thu, 21 Jun 2018 15:34:59 GMT

It should work. Is the 'Budget' field case sensitive ?

Re: Add condition

imtiazgirach — Thu, 21 Jun 2018 16:45:35 GMT

The 'Budget' has been typed in the expression exactly as is in the field but even if I use this expression it should exclude all the other categories but it brings back data for all the categories.

sum({<GLSubLedg.JournalCategory=['Permanent','Reversing']>}GLSubLedg.AmountCR-GLSubLedg.AmountDR)

Re: Add condition

trdandamudi — Thu, 21 Jun 2018 17:11:34 GMT

Try as below:

sum({<GLSubLedg.JournalCategory={'Permanent','Reversing'}>} GLSubLedg.AmountCR-GLSubLedg.AmountDR)

Re: Add condition

imtiazgirach — Thu, 21 Jun 2018 17:44:42 GMT

Still getting all the categories on the report. I'm attaching the screenshot

Re: Add condition

trdandamudi — Thu, 21 Jun 2018 17:56:00 GMT

For the expression "Amount", I still see you are using brackets instead of flower bracket. Are you using the below expression:

sum({<GLSubLedg.JournalCategory={'Permanent','Reversing'}>} GLSubLedg.AmountCR-GLSubLedg.AmountDR)

Also after changing to the above expression can you also uncheck the expression Amount CR and Amount_DR and let me know what you see.

Re: Add condition

imtiazgirach — Thu, 21 Jun 2018 18:18:22 GMT

I just copied and pasted the expression from your post. I'll try again and disable the amount cr and Dr expressions.

Re: Add condition

imtiazgirach — Thu, 21 Jun 2018 22:44:54 GMT

Still no joy. 'Budget' category lines still show. Screenshots attached

Re: Add condition

imtiazgirach — Fri, 22 Jun 2018 08:58:58 GMT

I've managed to sort this...for it to work I have to either add the AmountCR and AmountDR to the Dimensions or change the definition of the Amount_CR and Amount_CR as

sum({<GLSubLedg.JournalCategory={'Permanent','Reversing'}>}GLSubLedg.AmountCR)

and

sum({<GLSubLedg.JournalCategory={'Permanent','Reversing'}>}GLSubLedg.AmountDR)

Thanks so much for your help with this. Really appreciate it.

I need to add a condition on another report to bring back data for only those lines where the release date is null but if I use this expression I get no data at all

sum({<APHold.ReleaseDate={' '}>}APInv.InvoiceAmount)

Any ideas? Thanks again

Re: Add condition

Peter_Cammaert — Fri, 22 Jun 2018 09:45:02 GMT

Try with the inverse: excluding all lines for which ReleaseDate is not null. You'll have to make sure that ReleaseDate is actually NULL sometimes, and not just space or empty string. Those are not the same thing.

Sum({<APHold.ReleaseDate -= {"*"}>} APInv.InvoiceAmount)

Re: Add condition

imtiazgirach — Fri, 22 Jun 2018 09:55:23 GMT

I tried this expression and I get no data

Sum({<APHold.ReleaseDate -= {"*"}>} APInv.InvoiceAmount)

Re: Add condition

Peter_Cammaert — Fri, 22 Jun 2018 10:14:42 GMT

Sorry, my fault. You can't specify a set by keeping just the NULL values in a field.

Do you have another field that has values for all rows (like a Customer or Transaction ID)? Then you could use that one to filter out all lines without ReleaseDate value. For example:

Sum({<APInv.OrderNo = {"=IsNull(APHold.ReleaseDate)"}>} APInv.InvoiceAmount)