Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a column with multiple values in each field. I only want to report on certain values (e.g., Apple and Banana).
Fruit
Apple, Pear, Peach
Banana, Orange, Apple
Peach, Tomato, Kiwi
Apple, Orange, Peach
Within my app, I've been able to filter out the values I don't want using set analysis like this (and I'm getting the right result):
[Fruit]={
'*Apple*',
'*Banana*'
}
The problem is this causes some headaches when I'm trying to drill down using filters (I've connected some other tables with the [Fruit] column as well).
Can someone let me know if there's a way to filter out the unwanted values in the load script so none of the fruits I don't want are being loaded at all? I've searched the forums for Qlik Sense and Qlik View and have tried using the Wildmatch function, but I can't seem to get it work.
Any help would be much appreciated, and thanks!
Using SubField() is a great way if you want them split eventually anyways. But if did not want them split, WildMatch should work like this:
LOAD * INLINE [
Fruit
Apple, Pear, Peach
Banana, Orange, Apple
Peach, Tomato, Kiwi
Apple, Orange, Peach
] (delimiter is '|')
WHERE WildMatch(Fruit, '*Apple*', '*Banana*')
;
-Rob
I figured out a solution to my problem. Not using the logic I had mentioned in my original post, but by using a workaround (or maybe this is the best way?). This is what I did:
Instead of removing the fruits that I don't care about, I split them out into their own rows using SubField and then cleaned up the column to remove extra spaces, using LTrim, so the data comes in clean row-by-row.
Added this into my loadscript:
LTrim(SubField([Types of Fruit], ',')) as [Fruit]
Ideally, I'd love to be able to remove everything I don't want at the loadscript level. Still working on that!
Using SubField() is a great way if you want them split eventually anyways. But if did not want them split, WildMatch should work like this:
LOAD * INLINE [
Fruit
Apple, Pear, Peach
Banana, Orange, Apple
Peach, Tomato, Kiwi
Apple, Orange, Peach
] (delimiter is '|')
WHERE WildMatch(Fruit, '*Apple*', '*Banana*')
;
-Rob