topic Remove Records from QVD in QlikView

Remove Records from QVD

Wed, 18 May 2011 18:43:53 GMT

This is so simple yet I can't get it to remove records when loading a QVD. In the QVD there is a field "CustomerName", when I load the QVD I want to remove any records where the CustomerName contains the word "sample" no matter where it is located in the string. I have tried WHERE CustomerName <> '*sample*', WHERE CustomerName Not Like '*sample*' and others with no luck. What am I doing wrong?

Thanks,

Stephen

Remove Records from QVD

erichshiino — Wed, 18 May 2011 18:48:26 GMT

Try:

WHERE substringcount( CustomerName, 'sample')=0

Remove Records from QVD

stevedark — Wed, 18 May 2011 19:04:56 GMT

Hi Stephen,

The substringcount function will do the trick, as will index. Be aware though that this will cause the QVD to load in a non optimized mode. For a one of cleaning of your QVD this is fine, but if it is something you need in production then you should create a separate field in the QVD on creation called IsSample or something and then do a WHERE EXISTS on that.

Search the community for Optimized QVD Load and you should find plenty of articles.

- Steve

Remove Records from QVD

Wed, 18 May 2011 19:33:00 GMT

Thanks Erich and Steve. The substringcount worked. Depending on what we are using the table for, sometimes we need the 'Sample' accounts sometimes we don't.

Stephen