Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(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?
Hi Chris,
i think that the following exmaple i what you are looking for:
Timeline:
LOAD
text,
user_id,
user_name
FROM
(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
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
(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);
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.
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
(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.
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.
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) & ')';
Hi Chris,
i think that the following exmaple i what you are looking for:
Timeline:
LOAD
text,
user_id,
user_name
FROM
(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
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!