topic Re: Count with a condition in QlikView

Count with a condition

techvarun — Thu, 19 Sep 2013 07:11:36 GMT

Hi folks

I have a doubt in displaying the count from an excel sheet

I have an excel sheet with 3 fields

Date	Name	Status
22-Aug-12	DBA	AU
22-Aug-12	DBA	CU
22-Aug-12	DPR	AU
22-Aug-12	DPR	BU
22-Aug-12	DPR	DU
22-Aug-12	ABC	AU
22-Aug-12	ABC	CU
22-Aug-12	MNB	DU
22-Aug-12	MNB	AU
22-Aug-12	MNB	CU
22-Aug-12	MNB	DU
22-Aug-12	MNB	BU
22-Aug-12	MNB	DU

if i set a expression to count the number of entries for a Name DBA it is showing the count as 2 and its correct

But i need to find the count of entries based on status also for

eg : for DBA and AU the count is 1

and for MNB and DU count is 3

Regards

Re: Count with a condition

michael_maeuser — Thu, 19 Sep 2013 07:20:16 GMT

see attached, but i´m not sure if this is what you want.

Re: Count with a condition

techvarun — Thu, 19 Sep 2013 07:22:46 GMT

Sorry Michael

Currently am working from a Personal Edition QlikView

if possible please paste the expression here.

Regards

Re: Count with a condition

tresB — Thu, 19 Sep 2013 07:24:27 GMT

Can you please tell the output format you want?

Re: Count with a condition

Thu, 19 Sep 2013 07:29:30 GMT

Hi Please check the output suggested by - this is what you want

michael.maeuser

Re: Count with a condition

techvarun — Thu, 19 Sep 2013 07:32:20 GMT

Hi tresesco

This is my expected format

Regards

Re: Count with a condition

sushil353 — Thu, 19 Sep 2013 07:32:35 GMT

Hi,

Use a pivot table and use expression as

=aggr(Count(Status), Name, Status)

Please find attached

HTH

Sushil

Re: Count with a condition

kangaroomac — Thu, 19 Sep 2013 07:33:44 GMT

What you could do, is in your load script:

LOAD

Date, Name, Status

, Name & ' - ' & Status AS yourField....

Now in your front end, you can do a count on the created field, i.e. Count(yourField) should give you a count of unique joins for Name and Status.

Hope this helps.

Re: Count with a condition

tresB — Thu, 19 Sep 2013 07:35:25 GMT

Hello Bakshi,

If the dimension members(Name, Status) are there in the pivot table, aggr() function would not be required, only Count() would be enogh. Isn't it?

Re: Re: Count with a condition

tresB — Thu, 19 Sep 2013 07:38:10 GMT

PFA

Re: Count with a condition

techvarun — Thu, 19 Sep 2013 07:55:04 GMT

is there any possibility to do the same in straight table because I need the output in the format which i have mentioned in the image above.

And I need to show some different titles also for eg: for AU I need to show audit etc

Regards

Re: Count with a condition

techvarun — Thu, 19 Sep 2013 07:58:32 GMT

Count(status) for DBA will give me the result as 2 is there any way to set an expression like

count of DBA where Status = AU or something like that?

Regards

Re: Re: Count with a condition

tresB — Thu, 19 Sep 2013 08:05:46 GMT

You can. PFA.

But it is always recommended to do the mapping in the script and keep id dynamic.

Re: Count with a condition

Thu, 19 Sep 2013 08:12:56 GMT

Hi,

Create a pivot table with date, name, status as dimensions and Count(status) as expression.

And then drag the status column to horizontal (as Header).

Hope it helps.

Re: Count with a condition

Thu, 19 Sep 2013 09:36:02 GMT

find the attached doc

it gives correct data.

Date	Name	Status	=count(Name)
22-Aug-12	DBA	AU	1
	DBA	CU	1
	DPR	AU	1
		BU	1
		DU	1
	ABC	AU	1
	ABC	CU	1
	MNB	AU	1
		CU	1
		BU	1
		DU	3

Re: Count with a condition

Thu, 19 Sep 2013 11:13:54 GMT

Hello

When having a lot of data, it is a best practice to create a field in your table :

1 AS Used_For_Count.

Then you only need to SUM instead of COUNT, which Qlikview does much quicker!

And it can be used anywhere . Only drawback : no count distinct with this.

Hub