Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
May be use a calculated dimension -> SubField(Product, ' ', 1)
Expression -> Sum(Count)
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')
)
)
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
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.
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
Thanks for the help guys, will look into this.
William