Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try
...
WHERE Subject LIKE 'Module*';
if your subject starts with Module.
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.
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
try
...
WHERE Subject LIKE 'Module*';
if your subject starts with Module.
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
Hi,
I believe that the function 'WildMatch ()' functions.
where WildMatch (Subject,'Module*','Module?')
You tried to use it?
Rebeca