6 Replies Latest reply: Jun 3, 2016 12:46 AM by William Liang

# How to conditional sum based on value in the first column

Hello,

I have a question regarding conditional sum based on values in the first column. Is it possible through qlikview and if so how should I structure the expression in pivot table.

I have included a simplified example, the actual data set is more complicated therefore creating a new field of indicator would not be possible.

Based on this table

ProductCount
Blue Jeans10
White Shirt20
Black Jeans30
Blue Shirt40

I am trying to do an aggregate like below through wildcard search based on the first column (e.g. for Blue  Sum all counts that matches wildcard *Blue* )

ColorCount
Blue50
White20

Thanks!!

William

• ###### Re: How to conditional sum based on value in the first column

May be use a calculated dimension -> SubField(Product, ' ', 1)

Expression -> Sum(Count)

• ###### Re: How to conditional sum based on value in the first column

I would go with Sunny solution, otherwise you can add a nested "if" with wildmatch as a calculated dimension

if(wildmatch(Product,'*Blue*') =1, 'Blue',

if(wildmatch(Product,'*Black*') =1,'Black',

if(wildmatch(Product,'*White*') =1,'White')

)

)

• ###### Re: How to conditional sum based on value in the first column

Thanks guys.

The actual data set is more complicated than my example, the keyword is more diverse and over the place. Following on Ramon's suggestion, is there anyway to do wildcard search but automatically take in the values in column one as the 2nd input in the wildmatch function?

Thanks,

William

• ###### Re: How to conditional sum based on value in the first column

I agree with Sunny on this, it is better to add something this complex to the script instead of the front end,

try using MapSubstring to create a key between this field and the keywords

• ###### Re: How to conditional sum based on value in the first column

Thanks for the help guys, will look into this.

William