topic Re: Count Function in Qlikview in QlikView

Count Function in Qlikview

Mon, 19 Jan 2015 08:36:20 GMT

HI,

I have a table as follow:

Number	Transaction number	Item	Void
1	1001	Apple	Cancelled
2	1001	Banana
3	1001	Orange
4	1002	Strawberry	Change to Banana
5	1002	Biscuits
6	1002	Cake
7	1002	Candy
8	1003	Apple	Change to Candy
9	1003	Banana
10	1004	Banana
11	1004	Candy
12	1004	Orange
13	1005	Orange
14	1005	Candy
15	1005	Apple
16	1005	Banana

How can i create a pie chart like below?

At number 1 , When 'Cancelled' appeared at the 'Void" Field, row 1 will not be counted for apple.

At number 4, there is the 'Change to Banana' appeared in the 'Void" field, row 1 will be counted for Banana instead of Strawberry.

At number 8, the word 'Change to Candy' appeared, the row will be counted for Candy instead of apple.

Re: Count Function in Qlikview

datanibbler — Mon, 19 Jan 2015 09:04:51 GMT

Hi SIU,

so you basically need to take two steps here:

- First you have to find out what is the last word in the field "Void" if there is any - use the SUBFIELD() function to split
out the last word - in case the field is empty, that fct. will not fail, but will just return an empty field -> say, that field
will be named "Void_v2", ok?

- Then you can build a calculated field, using the fields "Item" and "Void" - if there is anything in the field "Voic" (or
   what you have made of it, the 3rd segment of it), use that instead of the value in "Item" - you can just drag along the
   value "Cancelled" for now and if it's there, replace it for the value in the field "Item" -> say that field will be named
    "Item_v2", ok?

=> Last step: You just count the values in the field "Item_v2" if there is any value other than "Cancelled", in which
case you don't count, so you could go like
>>> COUNT(IF(Item_v2 <> 'Cancelled', Item_v2)) <<<

HTH

Best regards,

DataNibbler

Re: Count Function in Qlikview

MarcoWedel — Mon, 19 Jan 2015 09:26:48 GMT

Calculated dimension:

If(Void<>'Cancelled',If(Index(Void,'Change to'),SubField(Void,'Change to ',2),Item))

Expression:

Count(Item)

Re: Count Function in Qlikview

Anonymous — Mon, 19 Jan 2015 10:20:15 GMT

Hi SIU,

Try below code in script:

Fruit:
Load * Inline
[
Number,Transaction number,Item,Void
1,1001,Apple,Cancelled
2,1001,Banana
3,1001,Orange 　
4,1002,Strawberry,Change to Banana
5,1002,Biscuits 　
6,1002,Cake
7,1002,Candy
8,1003,Apple,Change to Candy
9,1003,Banana
10,1004,Banana
11,1004,Candy
12,1004,Orange 　
13,1005,Orange 　
14,1005,Candy
15,1005,Apple 　
16,1005,Banana
];
NoConcatenate
FruitCount:
load *,
if(len(trim(Void)) = 0,Item) as Item1
Resident Fruit
where len(trim(Void)) = 0;

Concatenate

load *,
if(len(trim(Void)) <> 0 and Void <> 'Cancelled',right(Void,Len(Void)-Index(Void,' ',2))) as Item1
Resident Fruit
where len(trim(Void)) <> 0;

drop table Fruit;

In chart:

Dimension tab - select Item1

Expression tab -- Count(Item1)

Strawberry is replaced by banana,so there is no strawberry value in Pie chart

Regards

Neetha

Re: Count Function in Qlikview

Tue, 20 Jan 2015 02:17:23 GMT

Thank you for your answer, it's simple and worked well!

Re: Count Function in Qlikview

Tue, 20 Jan 2015 02:18:18 GMT

Thank you for your answer, it's really helpful!

Re: Count Function in Qlikview

Wed, 21 Jan 2015 02:37:56 GMT

Hello Marco,

Can you explain how the index() function and subfield() function work in qlikview? with the answer you provided as the example.

If(Void<>'Cancelled',If(Index(Void,'Change to'),SubField(Void,'Change to ',2),Item))

Thanks for your help.