Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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.

View solution in original post

6 Replies
sunny_talwar

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

Expression -> Sum(Count)



Capture.PNG

ramoncova06
Specialist III
Specialist III

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
Author

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

sunny_talwar

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
Specialist III
Specialist III

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
Author

Thanks for the help guys, will look into this.

William