topic Looping through existing table in load script in QlikView

Looping through existing table in load script

chrisbrain — Wed, 25 May 2011 13:52:37 GMT

Would appreciate some help with the following script problem. I am loading a table of tweets using something like:

Timeline:

LOAD

text,

user_id,

user_name

FROM

[http://www.pathtodata.com]

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

So at this point I have a table with the columns:

text user_id user_name

But what I want to then do is loop through this table and for each row (text value) I want to make a call to an API which returns the sentiment for the text, so I need to do something like the following pseudo-code:

foreach row in Timeline

Sentiment:

LOAD

status as sentiment_status,

text,

score as sentiment_score

FROM

[http://www.pathtosentimentapi.com/t={row.text}]

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

next row

Is this possible and is there a neat way of doing it?

Looping through existing table in load script

Wed, 25 May 2011 13:59:11 GMT

Hi,

Although I am not an expert, I think it would be easier (and much faster) to use a join between both data sources. I don't know how they are related, but if the user_id is used, it should be something like that

Timeline:

LOAD

text,

user_id,

user_name

FROM

[http://www.pathtodata.com]

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

join

LOAD

user_id,

status as sentiment_status,

text,

score as sentiment_score

FROM

[http://www.pathtosentimentapi.com/t={row.text}]

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

Looping through existing table in load script

chrisbrain — Wed, 25 May 2011 14:38:49 GMT

Thanks for the suggestion - would this call the second 'chunk' of script though once for each row in the Timeline table?

Can't see how the text property would correctly replace the {row.text} in my pseudo-code either.

Looping through existing table in load script

Wed, 25 May 2011 14:47:20 GMT

Umm sorry, I didn't notice the {row.text} in your example. So text is the field which has to match in both datasources. Then the example should be:

Timeline:

LOAD

text,

user_id,

user_name

FROM

[http://www.pathtodata.com]

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

join

LOAD

status as sentiment_status,

text,

score as sentiment_score

FROM

[http://www.pathtosentimentapi.com]

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

In a very high level explanation, both data sources will be read and for those rows where "text" is equal in both of them, sentiment_status and sentiment_score will be added as columns to Timeline.

Re: Looping through existing table in load script

chrisbrain — Wed, 25 May 2011 15:19:22 GMT

Now the text is not being passed to the second url:

[http://www.pathtosentimentapi.com/t={row.text}]

So the results from this request would always be the same. I think I need a for loop somewhere but in the example I have only seen looping through an array/comma separated list of through a range of integers. So I am wondering how I can loop through the rows in a table already loaded (e.g. Timeline) in my above example.

Re: Looping through existing table in load script

Wed, 25 May 2011 15:29:13 GMT

In that case, load Timeline and concatenate text field in a variable, which you can use in you loop following your example with a for each

let vtext ='=concat(text,' & chr(039) & ';' & chr(039) & ')';

Looping through existing table in load script

Wed, 25 May 2011 15:40:48 GMT

Hi Chris,

i think that the following exmaple i what you are looking for:

Timeline:

LOAD

text,

user_id,

user_name

FROM

[http://www.pathtodata.com]

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

LET rowText = NoOfRows('Timeline'); // get the total number of rows in Timeline table

for i=1 to $(rowText) // loop through every row

let text = FieldValue('text',$(i)); //get the value for "text" field on each row

//load the table from API call

Sentiment:

LOAD

status as sentiment_status,

text,

score as sentiment_score,

'$(rowText)' as CalledParam

FROM

[http://www.pathtosentimentapi.com/t=$(rowText)] // specify url param

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

Hope that helps!

Stefan

Re: Looping through existing table in load script

chrisbrain — Wed, 25 May 2011 20:09:31 GMT

Hi Stefan,

Great! I just needed to change the one line to:

[http://localhost:5555/QVSource/SentimentAnalyser/?table=Sentiment&Message=$(text)] // specify url param

To pass in the text parameter and it seems to be working

Thanks!