Looping through existing table in load script

[chrisbrain]

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?

[#2]

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

[chrisbrain]

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.

[#4]

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.

[chrisbrain]

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.

[#6]

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

[#7 · Accepted solution]

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

next

Hope that helps!

Stefan

[chrisbrain]

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!