topic Re: Need help to build an expression in QlikView

Need help to build an expression

Thu, 19 Nov 2015 15:49:53 GMT

Hello,

I need to count the number of distinct Order where Status<>'X' AND Quantity<>0.

There is the expression:

=count({<Status-={'X'} AND Quantity-={'0'}>} DISTINCT Order)

But unfortunately this syntax doesn't work, you can't put an 'AND' there...

I really dunno how to do this in a different way...

May some experts help me for this please?

Thanks,

Best Regards,

Loïc

Re: Need help to build an expression

swuehl — Thu, 19 Nov 2015 15:52:07 GMT

Mayb try

=count({<Status-={'X'}, Quantity-={'0'}>} DISTINCT Order)

Re: Need help to build an expression

Thu, 19 Nov 2015 15:56:54 GMT

Your expression exclude all the Status='X' and all Quantity='0'.

But I want to exclude only the lines where Status='X' and Quantity='0'.

Re: Need help to build an expression

manojkulkarni — Thu, 19 Nov 2015 15:57:55 GMT

count(distinct if(Status<>'X' and Quantity<>0,Order))

Re: Need help to build an expression

paolocamerini — Thu, 19 Nov 2015 16:06:00 GMT

I'd rather use

=count({<Status={'*'}-{'X'},Quantity={'*'}-{0}>}DISTINCT Order)

Tell us if it works

Re: Need help to build an expression

swuehl — Thu, 19 Nov 2015 16:08:45 GMT

You are right.

If you need to check on a per record base, you would need to use a key field that is unique for every combination of Status and Quantity per Order in the set analysis set modifier, or use the COUNT(IF(...) ) as ManojK has shown.

Re: Need help to build an expression

effinty2112 — Thu, 19 Nov 2015 16:10:53 GMT

Swuehl's suggestion =count({<Status-={'X'}, Quantity-={'0'}>} DISTINCT Order) looks good to me.

It's well worth it, when you are ready to tackle it, to become familiar with this very concise set analysis notation but it can be tricky until you get used to it.

<Status-={'X'}>

means recognise user selections in the Status field but remove records where Status = 'X'.

<Status=-{'X'}>

looks as if it is the same but it's not quite, note the position of '-'. is a shorthand for

<Status=*-{'X'}>

and when you think of it like this you can see that this means disregard user selections in the Status field, i.e. include all, but remove records where Status = 'X'.

Regards

Re: Need help to build an expression

Peter_Cammaert — Thu, 19 Nov 2015 16:13:07 GMT

How about this one?

=count({-<Status-={'X'}, Quantity-={'0'}>} DISTINCT Order)

Re: Need help to build an expression

Thu, 19 Nov 2015 16:18:43 GMT

I don't know why but i got the same result doing:

count(distinct if(Status<>'X' and Quantity<>0,Order))

count(distinct if(Status<>'X',Order))

It seems like a bug, the second argument is forgotten...

Re: Need help to build an expression

Thu, 19 Nov 2015 16:22:43 GMT

I don't think this could work, because in reality i have other fields that must be included in the same expression...

It's more like

=count({<Date={"<=$(varDate)"}, Status-={'X'} AND Quantity-={'0'}>} DISTINCT Order)

Where the Date value must be included and

Status and Quantity must be excluded.

Re: Need help to build an expression

swuehl — Thu, 19 Nov 2015 16:25:30 GMT

Maybe like

count({<Date={"<=$(varDate)"}>-<Status ={'X'}, Quantity ={'0'}>} DISTINCT Order)

Re: Need help to build an expression

Peter_Cammaert — Thu, 19 Nov 2015 16:26:31 GMT

Try this one.

=count({<Date={"<=$(varDate)"}>-<Status-={'X'}, Quantity-={'0'}>} DISTINCT Order)

And welcome to set analysis. Never assume that something is impossible without trying. You'll be as surprised as I am.

Re: Need help to build an expression

Thu, 19 Nov 2015 16:55:30 GMT

Thanks everyone for your responses.

For the moment i can't reach the result i want but it's maybe because of something else on the app, i dunno...

I'll try again on Monday

Re: Need help to build an expression

swuehl — Thu, 19 Nov 2015 17:33:55 GMT

If possible, please upload a small sample QV with some sample data and your requested outcome (count).

I believe it's much easier for everyone to help if we see where you are coming from and where you are heading to.

Data could be mock up data, if needed, but the model should represent your setting.

Re: Need help to build an expression

Peter_Cammaert — Mon, 23 Nov 2015 10:47:27 GMT

If you ever run into trouble with a document that is slow to react (usually because of a large amount of data), try the set analysis-approach. Embedding IF statements in an aggregation expression is highly inefficient when dealing with lots of rows.

Best,

Peter