Count with a condition

[techvarun]

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

[techvarun]

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

[techvarun]

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

[tresesco]

You can. PFA.

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

[#5]

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.

[#6]

Hi

find the attached doc

it gives correct data.

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

[#7]

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