Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using "Where" statement in the QV script

    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

 

1 Solution

Accepted Solutions
swuehl
MVP
MVP

try

...

WHERE Subject LIKE 'Module*';

if your subject starts with Module.

View solution in original post

5 Replies
swuehl
MVP
MVP

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.

Not applicable
Author

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 (Implementation)
Module 9:  CDARS On-boarding and Implementation (on-boarding)
Module 9:  CDARS On-boarding and Implementation (on-boarding)
Module 9:  CDARS On-boarding and Implementation (on-boarding)
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

swuehl
MVP
MVP

try

...

WHERE Subject LIKE 'Module*';

if your subject starts with Module.

Not applicable
Author

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

Not applicable
Author

Hi,

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

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

You tried to use it?

Rebeca