topic Generate new table based on resident in QlikView

Generate new table based on resident

Mon, 06 Jun 2011 09:12:58 GMT

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

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 09:46:38 GMT

Hello,

try this:

File:
LOAD * INLINE [
ID, Text
AA, the word is the best
];

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;

(with other keywords, but the result is the same)

Regarsds

Generate new table based on resident

Mon, 06 Jun 2011 09:49:48 GMT

Hi Martin,

Thanks for your suggestion. That's not a bad idea, so you just let them all auto concatenate into a new table Keywords.

I think this is effective when searching for a few words, what if my list of words grew to a more larger size, is there a more efficient way of doing this?

This will definitely work though, so I appreciate the suggestion!

Thanks

Gareth

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 09:54:49 GMT

Hello Gareth,

the do a loop and define the words in line Keywords:

File:
LOAD * INLINE [
ID, Text
AA, the word is the best
];
for each Keyword in 'word','best'
Keywords:
load ID,
if(isnull(wildmatch(Text,'*$(Keyword)*')), null(), '$(Keyword)') as Wordmatch
resident File;

next;

Regards

Generate new table based on resident

Mon, 06 Jun 2011 10:03:53 GMT

I've tried your suggestion and I stored my Keyword variable as:

SET Keyword='word;best';

Problem is I only get 1 row with Wormatch as 'word;best'.

I want to have an individual row for each word (like below):

AA, word

AA, best

Perhaps it is how I have defined my variable for the 'loop'?

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 10:10:12 GMT

it is not a good idea with the SET-command.

List your Keywords like

for each Keyword in 'word','best', 'word3', 'word4' ..... and so on.

Using a variable you need to configure the '

Generate new table based on resident

Mon, 06 Jun 2011 10:24:27 GMT

Hmm, I tried that just now and still only get one row:

AA 'word','best'

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 10:28:11 GMT

it issem to that there is another error in the script.

I get the right result:

Generate new table based on resident

Mon, 06 Jun 2011 10:30:55 GMT

Hello,

This could help you?

File:
LOAD * INLINE [
    ID, Text
    AA, the word is the best
    BB, the best word is
];

join

// Table containing all your searched words
LOAD * INLINE [
SearchedWord
word
best
];

Keywords:
load ID,
if(isnull(wildmatch(Text,'*'&SearchedWord&'*')), null(), SearchedWord) as Wordmatch
resident File;

regards,

Beyrem

Generate new table based on resident

Mon, 06 Jun 2011 11:26:28 GMT

Hi Beyrem,

This works well, although I get the null rows where there is no match. I ideally want to create a lookup table that only has IDs where there are matching words and a single word per row (as in your example).

If I had a long list of words and a really large table of the descriptions, this could get rather unfortunately large.

Thanks

Gareth

Generate new table based on resident

Mon, 06 Jun 2011 12:46:48 GMT

Hi Martin,

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

Thanks

Gareth

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 12:56:45 GMT

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

Generate new table based on resident

Mon, 06 Jun 2011 13:09:35 GMT

Sorry a bit confused.

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

Thanks

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 13:16:18 GMT

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

Generate new table based on resident

Mon, 06 Jun 2011 13:22:07 GMT

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

Generate new table based on resident

martinpohl — Mon, 06 Jun 2011 13:27:14 GMT

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.

Generate new table based on resident

Mon, 06 Jun 2011 13:43:00 GMT

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

Generate new table based on resident

pat_agen — Mon, 06 Jun 2011 13:54:33 GMT

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.

Generate new table based on resident

Mon, 06 Jun 2011 14:01:18 GMT

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

Cheers,

Generate new table based on resident

Mon, 20 Jun 2011 11:39:48 GMT

Hi Pat,

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

Thanks

Gareth