Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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
?¿??
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;
it works fine.
thanks very much for your help
regards
dcd
Glad it helped
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
Hi Marco
Thanks very much for your answer.
best regards
dcd