Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

halevinmd
New Contributor II

Still new to Qlik Sense. Having trouble with "if and Or"

Creating a calculated field to a table. I want to only sum only those sales transaction which have been characterized in any of 7 different ways that transaction should be added to the total.

If([Merchant-defined Order Status] = 'Completed' ,[Merchant-defined Order Status]),[product unit price}). This works but when I try to add other Merchant defined  statuses, such as shipped' using "or" it gives me an error of "unexpected token".

Tags (1)
11 Replies
martinpohl
Valued Contributor II

Re: Still new to Qlik Sense. Having trouble with "if and Or"

use set analysis

sum({<[Merchant-defined Order Status]={'Completed','Shipped'}>} Value)

regards

luismadriz
Valued Contributor

Re: Still new to Qlik Sense. Having trouble with "if and Or"

Hi Howard,

Martin's is the way to go as Set Analysis provide better performance and more flexibility, but sometime you need to use Ifs to get your head around some of that. This is an option depending of what you need to do:

SUM(If([Merchant-defined Order Status] = 'Completed' or [Merchant-defined Order Status] = 'Completed',ValueToSumOrAggregate))


Not sure what you want to do as you have [product unit price] which is not something that normally gets aggregated on its own, but it all depends on your design,


Cheers,


Luis

halevinmd
New Contributor II

Re: Still new to Qlik Sense. Having trouble with "if and Or"

Thank you for the help but still getting a error message "unrecognized symbol" {

sum({<[Merchant-defined Order Status] = 'Completed','Shipped','Awaiting Payment','Awaiting Shipment','Partially Shipped','Partially refunded'>}[Product Unit Price])
Any thoughts

luismadriz
Valued Contributor

Re: Still new to Qlik Sense. Having trouble with "if and Or"

sum({<[Merchant-defined Order Status] ={'Completed','Shipped','Awaiting Payment','Awaiting Shipment','Partially Shipped','Partially refunded'}>}[Product Unit Price])

halevinmd
New Contributor II

Re: Still new to Qlik Sense. Having trouble with "if and Or"

Luis,

I copied and pasted the line of code into the edit box and it still came up

with the left curly bracket "{" as an "unrecognized symbol" still

frustrating but there must be an explanation.

Howard Levin

On Sun, Jan 7, 2018 at 5:54 PM, Luis Madriz <qcwebmaster@qlikview.com>

luismadriz
Valued Contributor

Re: Still new to Qlik Sense. Having trouble with "if and Or"

Hi Howard,

As I know that expression is fine I'm thinking that you're in the load script where Set Analysts does not work

Do you want to try that expression in the front end?

If you still need to do it in the load script, can you please share your script and your requirement?

I hope this helps,

Cheers,

Luis

luismadriz
Valued Contributor

Re: Still new to Qlik Sense. Having trouble with "if and Or"

If, on the load script, the expression could be something like this:

SUM( IF(Match([Merchant-defined Order Status],'Completed','Shipped','Awaiting Payment','Awaiting Shipment','Partially Shipped','Partially refunded'),[Product Unit Price]) ) as SumOrdersWithStatus

When using this expression you may need to be mindful of the Group By at the end of the load for that table

I hope this helps,

Luis

Re: Still new to Qlik Sense. Having trouble with "if and Or"

Set Analysis is the way to go in most cases.  But if you must use an If(), testing multiple values is easier using match vs OR.

If(Match([Merchant-defined Order Status] ,'Completed', 'Shipped', 'Cancelled')....


See also MixMatch() and WildMatch().

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

halevinmd
New Contributor II

Re: Still new to Qlik Sense. Having trouble with "if and Or"

Luis,

I am in the section of Data manager editing a table by adding a calculated field not a load section. It may be that loading is where I need to be.

Howard

Community Browser