topic Re: compute time between tweet and reply in QlikView

compute time between tweet and reply

dcd123456 — Tue, 19 May 2015 16:14:00 GMT

Hello

i am testing qvsource connector for tweeter and want to compute the time between a tweeter and its reply.

there is a table like this

Search_created_at_timestamp_local	Search_id	Search_in_reply_to_status_id
11/05/2015 09:41:29	597667829014466561
11/05/2015 09:42:18	597668036955480065	597656467735506944
11/05/2015 09:49:15	597669784596180992	597666804216025088
11/05/2015 09:49:53	597669942742384640	597667829014466561
11/05/2015 09:50:39	597670138431807489
11/05/2015 09:53:31	597670859923398656	597666767603900416
11/05/2015 09:57:23	597671830472765440	597670138431807489
11/05/2015 10:03:42	597673420734730240	597671830472765440
11/05/2015 10:04:36	597673645864034305	597668036955480065
11/05/2015 10:27:41	597679458418589696	597673420734730240
11/05/2015 10:45:28	597683933107585024
11/05/2015 10:48:09	597684607371386880	597683933107585024
11/05/2015 10:54:42	597686256051552256	597298070532001793

i ha to look for the id of search_id_reply_to_status_id in the colum search id and make the diferents of the values of the first column of the two records.

for example:

11/05/2015 09:50:39

597670138431807489

11/05/2015 09:57:23

597671830472765440

597670138431807489

the tweet time is = 09:57:23 - 09:50:39

any ideas how to do this??,

thanks regards

dcd

Re: compute time between tweet and reply

ramoncova06 — Tue, 19 May 2015 16:21:12 GMT

do you want this change in the front end or the backend ?

I recommend doing this a backend, you can use applymap

Don't join - use Applymap instead

a front end option would be using p(), though depending on the amount of data this could not be a viable solution

Re: compute time between tweet and reply

dcd123456 — Tue, 19 May 2015 19:20:34 GMT

Hello Ramón

Coud you explain me how to use applymap in this example, i have three fields

Search_created_at_timestamp_local

Search_id

Search_in_reply_to_status_id

i can define a mappinng table like this

t_map:

Mapping LOAD Search_id, Search_created_at_timestamp_local

Resident t_tweets;

but now how i can use applymay() ????

i have to look for the value of the field Search_in_reply_to_status_id in the field Search_id and return Search_created_at_timestamp_local

?¿??

Re: compute time between tweet and reply

ramoncova06 — Tue, 19 May 2015 21:06:30 GMT

you would have to create another resident load once you have identified the field that you want, check the example below, I applied a null value for whatever the map could not find, you can change the to 'NA' or anything else you prefer

Temp:

load * Inline

[Search_created_at_timestamp_local , Search_id , Search_in_reply_to_status_id

11/5/2015 9:41 , 597667829014466561 ,

11/5/2015 9:42 , 597668036955480065 , 597656467735506944

11/5/2015 9:49 , 597669784596180992 , 597666804216025088

11/5/2015 9:49 , 597669942742384640 , 597667829014466561

11/5/2015 9:50 , 597670138431807489 ,

11/5/2015 9:53 , 597670859923398656 , 597666767603900416

11/5/2015 9:57 , 597671830472765440 , 597670138431807489

11/5/2015 10:03 , 597673420734730240 , 597671830472765440

11/5/2015 10:04 , 597673645864034305 , 597668036955480065

11/5/2015 10:27 , 597679458418589696 , 597673420734730240

11/5/2015 10:45 , 597683933107585024 ,

11/5/2015 10:48 , 597684607371386880 , 597683933107585024

11/5/2015 10:54 , 597686256051552256 , 597298070532001793];

Mapping

Reply:

load Search_in_reply_to_status_id,

Search_created_at_timestamp_local

Resident Temp;

Final:

load

ApplyMap('Reply',Search_id,null()) as ReplyTime

Resident Temp;

drop table Temp;

Re: compute time between tweet and reply

dcd123456 — Wed, 20 May 2015 19:32:59 GMT

it works fine.

thanks very much for your help

regards

dcd

Re: compute time between tweet and reply

ramoncova06 — Wed, 20 May 2015 19:37:50 GMT

Glad it helped

Re: compute time between tweet and reply

MarcoWedel — Fri, 22 May 2015 23:55:50 GMT

Hi,

another solution could be:

table1:
CrossTable (TimeStampType, ID)
LOAD Search_created_at_timestamp_local as TimeStamp, 
     Search_id as Start, 
     Search_in_reply_to_status_id as End
FROM [https://community.qlik.com/thread/164634] (html, codepage is 1252, embedded labels, table is @1);

table2:
Generic LOAD
  ID,
  TimeStampType,
  TimeStamp
Resident table1;

table3:
LOAD Distinct ID Resident table1 Where Len(ID);

DROP Table table1;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'table2.*') THEN
  LEFT JOIN (table3) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

Left Join (table3)
LOAD Distinct
  Start,
  End,
  Interval(End-Start) as TweetTime
Resident table3;

hope this helps

regards

Marco

Re: compute time between tweet and reply

dcd123456 — Tue, 26 May 2015 11:13:16 GMT

Hi Marco

Thanks very much for your answer.

best regards

dcd