topic Re: set analysis and using and/or in QlikView

set analysis and using and/or

Mon, 23 Sep 2013 13:56:44 GMT

I want to

select

count(id)

where

field 1 like 'Inventory'

or field 2 = 'Inventory'

I need this in a chart expression. I know that I can

field 1 = {*Inventory*},

field 2 = {'Inventory')}

likewise,

field 1 = {*Inventory*},

field 2 +={'Inventory') or -= or /=

I need to make sure that I count field one is inventory or where filed 2 is inventory.

I am quite new to QlikView and any help would be appreciated.

Re: set analysis and using and/or

CELAMBARASAN — Mon, 23 Sep 2013 14:18:21 GMT

Try with this

Count({<Field1={*Inventory*}> + <Field2={'Inventory'}>} FieldName)

Re: set analysis and using and/or

srchilukoori — Mon, 23 Sep 2013 14:21:30 GMT

I don't know if I understood your question properly but try this expression.

=Count({<Field1 = {'Inventory'}>} ID) + Count({<Field2 = {'Inventory'}>} ID) - Count({<Field1 = {'Inventory'}, Field2 = {'Inventory'}>} ID)

This expression gives you the count of ID, where only one of Filed1 and Field2 is equal to "Inventory"

Re: set analysis and using and/or

Carlos_Reyes — Mon, 23 Sep 2013 14:29:53 GMT

I haven't tested this but I would try:

Count( { $ < id = { " [Field 1] = 'Inventory' OR [Field 2]='Inventory' " } > } id)

Hope it works.

Re: set analysis and using and/or

srchilukoori — Mon, 23 Sep 2013 14:39:30 GMT

Minor correction the above expression is a definition of (A union B) = either A or B or (A & B).

If you are looking for only (A or B), use the modified expression:

=Count({<Field1 = {'Inventory'}>} ID) + Count({<Field2 = {'Inventory'}>} ID) - 2*Count({<Field1 = {'Inventory'}, Field2 = {'Inventory'}>} ID)

Re: set analysis and using and/or

jagan — Mon, 23 Sep 2013 14:39:42 GMT

Hi Robert,

Try this

=Count({<Field1={'*Inventory*'}> + <Field2={'Inventory'}>} FieldName)

Regards,

Jagan.

Re: set analysis and using and/or

Mon, 23 Sep 2013 14:53:54 GMT

This looks promising. However, it gives me a syntax error.

I tried this

count({<wr.Short_Description = {'*Inventory*'}> + wr.Work_Request_Type = {'Inventory'} >} Work_Request_ID)

//Count({<Field1={*Inventory*}> + <Field2={'Inventory'}>} FieldName)

I maybe missing something in my syntax.

Re: set analysis and using and/or

Mon, 23 Sep 2013 15:06:31 GMT

No syntax error. However, not giving me the field 1 or field 2. I am getting 0 when I should have at least 68 where the field 1 like *Inventory*

Re: set analysis and using and/or

Mon, 23 Sep 2013 15:07:45 GMT

I am looking counts where have A or B. I will test this to see the result.

Re: set analysis and using and/or

CELAMBARASAN — Mon, 23 Sep 2013 15:10:47 GMT

You have missed on angular bracket before wr.Work_Request_Type

count({<wr.Short_Description = {'*Inventory*'}> + <wr.Work_Request_Type = {'Inventory'} >} Work_Request_ID)

Re: set analysis and using and/or

MayilVahanan — Mon, 23 Sep 2013 15:19:44 GMT

Try like this

count({<wr.Short_Description = {'*Inventory*'}> + <wr.Work_Request_Type = {'Inventory'} >} Work_Request_ID)

Re: set analysis and using and/or

jagan — Mon, 23 Sep 2013 15:23:55 GMT

Hi,

Try like this

=Sum(Aggr( If(wr.Short_Description Like '*Inventory*' or wr.Work_Request_Type ={'Inventory', 1, 0), Work_Request_ID))

Hope this helps you.

Regards,

Jagan.

Re: set analysis and using and/or

Mon, 23 Sep 2013 15:27:20 GMT

This is working. It is giving me short descriptions for inventory and say order as well as the ones for work request type as inventory. Interesting that field one union field 2 and then exclude? minus.

What remains is to have this inside an ordinary set analysis. Say,

field 3 = {'Status','Create'} to further restrict the data in the expression. That is to say, where field 3 in status,create.

Syntax is killing me in the set analysis. I have read qlikview 11 for developers. It is the complex sets that are driving me mad. I have worked in Tableau, Cognos, MicroStratedgy, and SSRS. I know that it will just take some time and experience.

Thanks to all for your help

Re: set analysis and using and/or

tresB — Mon, 23 Sep 2013 15:29:15 GMT

Yes, you missed '<'. try:

count({<wr.Short_Description = {'*Inventory*'}> + <wr.Work_Request_Type = {'Inventory'} >} Work_Request_ID)

Re: set analysis and using and/or

Mon, 23 Sep 2013 15:51:42 GMT

This is probably the final answer to my discussion. I will mark srchilukoori as the correct answer. Here is my final expression.

count({<wr.Short_Description ={'*Update Inventory*'},
wrTracking.affectedField={'Status','Create'},
wrTracking.Char_Status -= {'Pending Close','Closed'}
>} Work_Request_ID)

+
count({<wr.Work_Request_Type = {'Inventory'},
wrTracking.affectedField={'Status','Create'},
wrTracking.Char_Status -= {'Pending Close','Closed'}
>}Work_Request_ID)

-2*
count({<wr.Short_Description ={'*Update Inventory*'}, wr.Work_Request_Type = {'Inventory'},
wrTracking.affectedField={'Status','Create'},
wrTracking.Char_Status -= {'Pending Close','Closed'}
>} Work_Request_ID)

Re: set analysis and using and/or

Mon, 23 Sep 2013 16:15:06 GMT

srchilukoori. Is the -2* saying exclusion and then intersection? Sometimes, the - and * get mixed up in my head. so, thanks.