Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

1 Solution

Accepted Solutions

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

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.

6 Replies

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

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

Expression -> Sum(Count)



Capture.PNG

ramoncova06
Valued Contributor III

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

      )

)

Not applicable

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

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.

ramoncova06
Valued Contributor III

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

Not applicable

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

Thanks for the help guys, will look into this.

William

Community Browser