Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Transfer Values into variable (peek)

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

Tags (3)
1 Solution

Accepted Solutions

Re: Transfer Values into variable (peek)

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)');


talk is cheap, supply exceeds demand
5 Replies
kumarnatarajan
Valued Contributor

Re: Transfer Values into variable (peek)

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

multiview
Contributor II

Re: Transfer Values into variable (peek)

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

Re: Transfer Values into variable (peek)

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)');


talk is cheap, supply exceeds demand
Not applicable

Re: Transfer Values into variable (peek)

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.

Not applicable

Re: Transfer Values into variable (peek)

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

Community Browser