Generate new table based on resident

[#1]

Hi Guys,

I'm sure this is a simple question, but I am having a mind blank, hopefully someone can help me.

I have an existing table with a description (text) field.  I want to generate a new table that has the UID and a few specific words I find in the description field with duplication (i.e. a new row per word that I'm searching for).

So for example with 1 row:

ID, Text

AA, The quick brown fox jumps over the lazy dog

I would use the wildmatch to search for the words of interest, in this case I'm searching for brown, over and lazy.

So I want another table to generate that links on ID that would now have 3 rows because I found my 3 words:

ID, Wordmatch

AA, brown

AA, over

AA, lazy

Can anyone think of an ingenious way to do this efficiently?

Thanks

Gareth

[#2]

Hi Martin,

Can you please copy/paste what your variable looks like?

Thanks

Gareth

[martinpohl]

I define the loop with

for each Keyword in 'word','best'

so in loop 1 the variable "Keyword" is word, in loop 2 it is best.

There is no other variable.

Regards

[#4]

Sorry a bit confused.

So when you refer to $(Keyword), where is that variable pulling from and what does it look like?

Thanks

[martinpohl]

you list the values in the line

for each Keyword in 'value1', 'value2', 'value3' .... and so on

then the loop uses each value in this list and set it to the variable $(Kalendertag)

But you won't see a variable within all values of the loops

[#6]

So do you mean recreating the second table code for each keyword?

e.g.:

Keywords:

load ID,

if(isnull(wildmatch(Text,'*word*')), null(), 'word') as Wordmatch

resident File;

load ID,

if(isnull(wildmatch(Text,'*best*')), null(), 'best') as Wordmatch

resident File;

I was confused because you used the term 'loop' which to me implies an automated process, this is a repeated statement for each keyword.  Was this what you meant?  Sorry if I've completely missed something.

Cheers,

Gareth

[martinpohl]

you do missunderstand

you just need to program one time.

The line

if(isnull(wildmatch(Text,'*$(Keyword)*'),null(),'$(Keyword)') as Wordmatch

gives you the value the loop has actual find.

[#8]

Ok thanks, so my confusion is where and how $(Keyword) is stored - this to me is referring to a variable, so how has your Keyword variable been stored?

Thanks

G

[pat_agen]

Hi Gareth,

taken from page 296 of the qv Refernce Manual:

For Each..Next

The for each..next control statement creates a loop which executes for each

value in a comma separated list. The statements inside the loop enclosed by

for and next will be executed for each value of the list. Special syntax makes

it possible to generate lists with file and directory names in the current directory.

Martin posted the correct code earlier on oin this thread. He chose "Keyword" as his varaible but coudlhave called it anything he wanted. The For Each .. Next loop does not require thta the vraible be declared beforehand with a set/let comand.

for each Keyword in 'word','best'

     Keywords:

     load ID,

     if(isnull(wildmatch(Text,'*$(Keyword)*')), null(), '$(Keyword)') as Wordmatch

     resident File;

next;

hope this helps - give Martin his 10 points!

Interesting thread - I think you shoudl add to Martin's code a where clause on your load command to avoid the creation of records with null in the WordMatch field.

[#10]

Ah thanks for the clarification.  I wasn't even aware that loops were possible!  This is great news for me.

Cheers,

G

[#11]

Hi Pat,

I just wondered if you could clarify what you would include on your where clause for this code?

Thanks

Gareth