topic Re: Count in QlikView

Count

Fri, 19 Dec 2014 13:49:34 GMT

Hi,

I have a col having YES and NO as data.I want to count no.of YES and NO

if(col='YES',count(col) ) as col1;

but it is showing error.

Can anyone help me please.

Re: Count

Anonymous — Fri, 19 Dec 2014 13:52:47 GMT

Hello,

You can do this at front end, not in the script (in the script it is harder and inefficient).

count({<col={'YES'}>} col)

This technique is called Set Analysis. You can check in help.

Serhan

Re: Count

Anonymous — Fri, 19 Dec 2014 13:54:14 GMT

Hi,

are you using group by in the script

Re: Count

Anonymous — Fri, 19 Dec 2014 14:02:30 GMT

Hi,

use serhan's solution in UI:

count({<col={'YES'}>} col)

in script you can use:

Table:
Load * inline [Ref,Product,Value,col
1,111,700,yes
2,111,400,no
1,115, 1000,yes
2, 116,800,no
3, 116, 600
3, 141, 900
4, 150, 200
4, 181, 100
5, 182, 400];

NoConcatenate
LOAD *,
col as YesCal
Resident Table
where col = 'yes';
concatenate
LOAD *,
col as NoCal
Resident Table
where col = 'no';

in UI:

=count(NoCal)

Regards

Neetha

Re: Count

its_anandrjs — Fri, 19 Dec 2014 14:06:45 GMT

Hi,

1. You can use the SET analysis in the application like

count({Col={'YES'}} Col) And count({Col={'NO'}} Col)

2. In the load script also as you create another fields

Tab1:

Load Col From Source;

Load Id,

Count( If(Col='YES', Col )) as YesCount,

Count( If(Col='NO', Col )) as NoCount

Resident Tab1 Group BY Id;

Hope this helps

Regards

Anand

Re: Count

Fri, 19 Dec 2014 14:25:51 GMT

Thanq Anand

Continuing this.. i have 3YesCounts for 3different tables withe 3residents .how to get sum of these 3YesCounts?

Re: Count

ankitaag — Fri, 19 Dec 2014 14:52:18 GMT

Hi Ramya,

You can concatenate those three resident tables and then sum the counts in another table using the resident. Also, if those three tables are different from each other then at the front end you can simply use

=sum(YesCount1)+sum(YesCount2)+sum(YesCount3)

andsame with theNoCounts.

Attached is the example of the same.

Thanks and Regards,

Ankita

Re: Count

its_anandrjs — Fri, 19 Dec 2014 14:57:42 GMT

Hi,

If you have any sample then please provide with few rows or you can simply concatenate them if fields names are same otherwise rename them

Load YesCountField Resident 1;

Concatenate

Load YesCountField Resident 1;

Concatenate

Load YesCountField Resident 1;

And in Front end do sum.

Regards

Anand