topic Re: Using "Where" statement in the QV script in QlikView

Using "Where" statement in the QV script

Wed, 12 Jun 2013 16:12:52 GMT

Good morning all,

I have set up QV to load a spreadsheet for manipulation bny our client services people. The source document does not contain all the fields they would like to sort by so I want to use a "where" statement in the script to search the subject column for specific text and only load the rows where the column does contain the text.

Here is what I have so far:

Directory;

LOAD Subject,
     StartDate,
     StartTime,
     EndDate,
     MeetingOrganizer,
     RequiredAttendees,
     OptionalAttendees,
     Description,
     Location

FROM
[Mistys Calendar.xls]
(biff, embedded labels, table is [Calendar$])

WHERE mixmatch(Subject,'module')>0;

There are no errors when I load the script but no data gets loaded either.

What am I doing wrong with this script? If I remove the "Where" statement, then the data all loads normally

What I want is to load only those rows where the "Subject" column contains the word "Module.

Any thoughts?

Thanks, Steve

Re: Using "Where" statement in the QV script

swuehl — Wed, 12 Jun 2013 16:26:06 GMT

Could you upload some sample values of your Subject field, or even better, the Excel file itself?

mixmatch() performs a case insensitive comparision, so will find 'module' or 'Module', but not 'Module 1'.

You'll need to use wildmatch() or like string operator for doing a wildcard search.

Re: Using "Where" statement in the QV script

Wed, 12 Jun 2013 16:54:35 GMT

Here are some examples of lines in thje "Subject" column that I want to capture:

Module 9: CDARS On-boarding and Implementation (Implementation)

Module 9: CDARS On-boarding and Implementation (on-boarding)

Name Changes

Natalie's Training

National Concert

New hire training touch base

There are many "Module" numbers. I had thought that since there was a space between Module and the numbers that Module would be treated as a free standing word.

Steve

Re: Using "Where" statement in the QV script

swuehl — Wed, 12 Jun 2013 18:21:47 GMT

try

...

WHERE Subject LIKE 'Module*';

if your subject starts with Module.

Re: Using "Where" statement in the QV script

Wed, 12 Jun 2013 18:43:56 GMT

That did the trick!

Thanks a ton.

Let me ask you. How would I need to do this if I needed to search a phrase or string with more than one word?

Steve

Re: Using "Where" statement in the QV script

Wed, 12 Jun 2013 18:45:32 GMT

Hi,

I believe that the function 'WildMatch ()' functions.

where WildMatch (Subject,'Module*','Module?')

You tried to use it?

Rebeca