Transfer Values into variable (peek)

[#1]

hello all,

I've got a Problem using a variable in QV-Script.

My plan was to load a bunch of names from a txt file and then search for entrys in a large table via SQL with this name. I tried to transfer the selected names into a variable with a peek. But the variable only shows the last value of the list of names.

Hope someone can help me out.

My script:

Namen:

LOAD names

FROM

...\names.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Let vNumberOfRows = NoOfRows('Namen');

For vZ = 0 to (vNumberOfRows - 1)

Let vNAMES = Peek('names',vZ,'Namen');

Let [$(vNAMES)] = Peek('names',vZ,'Namen');

Next

And then the select in the large table:

DATA:

LOAD *;

SQL SELECT *

FROM "xxx.xxxx"

WHERE names in ('$vNAMES');

Hope you can help me out.

Thanks

[kumarnatarajan]

Hi,

1. your for loop variable [$(vNAMES)] wil have last record.

Ans:

1. So you need to Concatenate names in  [$(vNAMES)] with comma seperator

2. Then use wildmatch in load script to select the names.

Thanks & Regards,

Kumar Natarajan

[Anonymous]

If you expect it to run for each and every value then you need to place the LOAD..SELECT statement inside the LOOP. You may use ADD LOAD so it appends the values from the database table to the same table.

I hope this helps!

MultiView

[Gysbert_Wassenaar · Accepted solution]

As mentioned by Kumar you need to use the concat function. Try something like below:

Namen:

LOAD names

FROM

...\names.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Temp:

load concat(chr(39) & names & chr(39),',') as namelist resident Namen;

LET vNAMES = peek('namelist');

DATA:

LOAD *;

SQL SELECT *

FROM "xxx.xxxx"

WHERE names in ('$(vNAMES)');

[#5]

thanks a lot.

Now I've got the problem of not knowing how qlikview/sql likes to have the values in my new variable seperated. I tried to build a work-around with giving qv the text-string of my variable.

Now I'm running into the maximum length of the variable.

[#6]

Solved the Problem. Silly mistake. Just forgot the blank in the concat part. An changed the way of calling the variable in the sql part like this:

Load concat(chr(39) & Names & chr(39),', ') as NameListe

...WHERE Name IN ($(vNames))

thanks for the solution