topic Re: Help with filtering unwanted data in load script? in App Development

Help with filtering unwanted data in load script?

drminaker — Mon, 19 Oct 2015 21:27:00 GMT

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!

Re: Help with filtering unwanted data in load script?

drminaker — Tue, 17 Nov 2015 18:36:02 GMT

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!

Re: Help with filtering unwanted data in load script?

rwunderlich — Tue, 17 Nov 2015 19:09:47 GMT

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

http://masterssummit.com

http://qlikviewcookbook.com