topic Re: Help with an expression? in QlikView

Help with an expression?

Anonymous — Thu, 08 Jun 2017 17:24:43 GMT

Hi,

I have the following expression that I need to understand, can you please let me know what do mean each section:

= Count(Aggr(Only({$<ID = e({<[Closure Code]={'wrong module','Transferred to new module'}>})>} [Case Count]), AutoID))

I know that the on red are fields, but I need to know what does the blue words/letters. I need to add exclusions for other fields, can I just add a coma and will work?

Please advise.

Re: Help with an expression?

sunny_talwar — Thu, 08 Jun 2017 17:34:56 GMT

This is basically saying that Count DISTINCT Case Count per AutoID where a particular id doesn't have Closure Code as wrong module or transferred to new module.

and yes, to add more exclusions you need to add them in the list separated by comma

Re: Help with an expression?

Anonymous — Thu, 08 Jun 2017 18:06:24 GMT

The problem is that instead of decreasing the numbers are increasing them. :S

Do you know another way to do it, to see if I get the same results?

Re: Help with an expression?

sunny_talwar — Thu, 08 Jun 2017 18:16:55 GMT

Difficult to say without looking at a sample? Can you provide some sample data with the expected output?

Re: Help with an expression?

Anonymous — Thu, 08 Jun 2017 18:55:09 GMT

Cannot provide a lot of information, however this is the original calculation:

= Count(Aggr(Only({$<ID = e({<[Closure Code]={wrong module','Transferred to a new module'}>})>} [Case Count]), AutoID))

Then, I update as the following to get what I though a new filter will decrease the number, but happened the opposite:

=Count(Aggr(Only({$<CustomID = e({<[Closure Code]={'wrong module','Transferred to a new module'},[State] = {'Pre','Invalid'}>})>} [Case Count]), AutoID))

I have noticed that the ID won't relate the fields Closure Code or State, so I use another field that relates both fields. But I am getting extra records.

I need to count distinct all records that aren't wrong module, transferred to a new module, or with the state on Pre or invalid.

Can you help me with a expression that can do something like that?

Re: Help with an expression?

neelamsaroha157 — Thu, 08 Jun 2017 19:37:19 GMT

what results are you getting with below expression

= Count(Aggr(Only({$<ID = e({<[Closure Code]={wrong module','Transferred to a new module'}>}),[State] = {'Pre','Invalid'}>} [Case Count]),AutoID))

Re: Help with an expression?

Anonymous — Thu, 08 Jun 2017 19:44:52 GMT

I am getting more records that the one without the state field. That's why I am thinking it's not working well. Because should exclude both.

Re: Help with an expression?

neelamsaroha157 — Thu, 08 Jun 2017 20:00:52 GMT

Try this

= Count(Aggr(Only({$<ID = e({<[Closure Code]={wrong module','Transferred to a new module'}>}),[State] -= {'Pre','Invalid'}>} [Case Count]),AutoID))

Re: Help with an expression?

Anonymous — Thu, 08 Jun 2017 20:40:50 GMT

I did, didn't work, if I uses only one condition inside the e() it works, but when I add the ',' and the second field it excluded both conditions

Re: Help with an expression?

Anonymous — Fri, 09 Jun 2017 16:42:04 GMT

Any idea on how to have 2 exclusions in one expression? when I have added the second field none of the exclusions worked.

Re: Help with an expression?

sunny_talwar — Fri, 09 Jun 2017 16:43:48 GMT

Can you prep up some mock up data and try to explain what do you need as a result from your data?

Re: Help with an expression?

vishsaggi — Fri, 09 Jun 2017 16:54:34 GMT

Just wondering, you might have tried.?

In neelams expression he is not using the State field exclusion inside the e() function..Did you try using State exclusion as separate exclusion like he mentioned.?

May be try this?

= Count(Aggr(Only({$< ID = e({<[Closure Code]={wrong module','Transferred to a new module'}>}),

[State] = {'*'} - { 'Pre', 'Invalid'} >} [Case Count]) , AutoID))

Re: Help with an expression?

Anonymous — Fri, 09 Jun 2017 17:19:16 GMT

Sure, let's say that I have this table (there should be further fields, but this is an example)

Purpose	YRMON	State	ID	Closure code	Total
Test	2017-06	Wrong module	1111	Completed	1
		Proceed	1112	Completed	1
		Proceed	1113	Pre	1
		Proceed	1114	Completed	1
Printing	2017-06	Transferred to a new module	1115	Completed	1
Printing	2017-06	Proceed	1116	Completed	1
Business	2017-06	Proceed	1117	Completed	1
		Proceed	1118	Completed	1
		Proceed	1119	Invalid	1
Sales	2017-06	Proceed	1120	Completed	1
Sales	2017-06	Proceed	1121	Completed	1

If I used the condition: = Count(Aggr(Only({$<ID = e({<[State]={wrong module','Transferred to a new module'}>})>} [Case Count]), AutoID)), it worked fine, the records with the state wrong module and transferred to a new module are being removed from the table. If I added the other expression separated by coma, the filters won't work. I will need a condition that will count everything that hasn't Wrong module or transferred to a new module on state field and pre and invalid on closure code.

Please let me know if this helps.

Re: Help with an expression?

vishsaggi — Fri, 09 Jun 2017 19:48:26 GMT

So here technically your count should be 9 for first expression and 7 after excluding closure code. I am not sure if this is a good way of doing it. But may be you can try like?

= Count({$< ID = E({< State = {'Wrong module','Transferred to a new module'} >}) >} ID) -

Sum(Aggr(Count({$< Closurecode = {'Pre', 'Invalid'} >} ID), ID))

if you want to use Only()

= Count(Aggr(Only({$< ID = E({< State = {'Wrong module','Transferred to a new module'} >}) >} ID), ID)) -

Count(Aggr(Only({$< Closurecode = {'Pre', 'Invalid'} >} ID), ID))

Re: Help with an expression?

sunny_talwar — Fri, 09 Jun 2017 21:30:27 GMT

Few questions for you

1) A single ID can have multiple state?

2) What is AutoID here? You mention that in your expression but I don't see it in the sample data? Is it the same thing as ID?

3) What is the expected output based on the data that you have provided?

Re: Help with an expression?

Anonymous — Mon, 12 Jun 2017 13:55:17 GMT

The second expression looks like working. I am testing it.

Re: Help with an expression?

Anonymous — Mon, 12 Jun 2017 14:00:38 GMT

1) No, a single ID can only have 1 state

2) AutoID is the ID of the request, that is related to the State or Closure Code (I just removed the 'Auto' for make the example easiest)

3) The idea is that the expression count all the requests that have the State different to 'Wrong Module' and 'Transfer to a new module', and if the state is different then don't count the Closure codes equals to invalid or pre.

I mean, there are 5 requests

1 has WM as state

1 has Pre as closure code

1 has TTANM and invalid

The expression should count 2 records.

Re: Help with an expression?

sunny_talwar — Mon, 12 Jun 2017 14:12:20 GMT

When you say 5 request? What is a request here? ID? I see 11 IDs... or is it Purpose? But again there are only 4 Purposes.... How do you determine a request in your supplied data?

Re: Help with an expression?

Anonymous — Mon, 12 Jun 2017 14:17:28 GMT

In the example that I have provided, should only display 7 records (1112,1114, 1116,1117,1118,1120,1121)

Re: Help with an expression?

sunny_talwar — Mon, 12 Jun 2017 14:22:56 GMT

This gives me 7

=Count(DISTINCT {<State -= {'Wrong module', 'Transferred to a new module'}, [Closure code] -= {'Pre', 'Invalid'}>} ID)