topic Re: How to conditional sum based on value in the first column in QlikView

How to conditional sum based on value in the first column

Tue, 31 May 2016 08:21:37 GMT

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

Product	Count
Blue Jeans	10
White Shirt	20
Black Jeans	30
Blue Shirt	40

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* )

Color	Count
Blue	50
White	20

Thanks!!

William

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

sunny_talwar — Tue, 31 May 2016 14:53:28 GMT

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

Expression -> Sum(Count)

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

ramoncova06 — Tue, 31 May 2016 16:15:01 GMT

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

Wed, 01 Jun 2016 15:30:20 GMT

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

sunny_talwar — Wed, 01 Jun 2016 15:44:34 GMT

May be create a mapping load to do this in the script.

http://www.learnqlickview.com/a-qlikview-tutorial-mapping-load-in-qlikview/‌

or use an inline table like this:

LOAD * INLINE

Product, Group

Blue Jeans, Blue

White Shirt, White

Black Jeans, Black

Blue Shirt, Blue

...

];

and now use Group as your dimension.

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

ramoncova06 — Thu, 02 Jun 2016 21:19:45 GMT

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

Fri, 03 Jun 2016 04:46:05 GMT

Thanks for the help guys, will look into this.

William