Count Function in Qlikview

[#1]

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.

                                                                                

[datanibbler]

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

[MarcoWedel · Accepted solution]

Calculated dimension:

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

Expression:

Count(Item)

[Anonymous]

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

[#5]

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

[#6]

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

[#7]

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.