Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

qlikview facebook application conversion to V2 connector error

i have tried to convert my facebook application script to match V2 , as following, but it still returing an error

1-

"Table not found

left Join LOAD * Resident FacebookFanPagesConnectorV2_CommentCountForItem

2-

Table not found

Left Join LOAD * Resident FacebookFanPagesConnectorV2_LikeCountForItem

3-

Table not found

DROP TABLES statement

4-

Table not found

STORE FB_Page_Comments_Table INTO C:\SocialBlink\Facebook\QVDs_Philips\FB_Page_Comments_Table.qvd (qvd)



THE SCRIPT:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

let fb_page_Summary = 'FB_Page_Summary_Table.qvd';

let size = filesize('$(vDestination)\$(fb_page_Summary)');

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

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

   

  for i=0 to $(noRows)-1 // loop through every row

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

  FB_Page_Summary_Table:

  LOAD

  Hash128(page,'-','$(vTodayAsDate)') as Key_1,

  //Added Fields//

  //'$(vToday)' as Page_loaddatetime,

  '$(vTodayAsDate)' as Page_loaddate,

  //'$(vTodayAsTime)' as Page_loadtime,

  // Default Fields //

  page as page_summary,

  //updated_time as Page_updated_time,

  //timestamp#(mid(updated_time, 1, 10) & mid(updated_time, 12, 8), 'YYYY-MM-DDhh:mm:ss') as Page_updated_time_qvdatetime,

  //date#(subfield(updated_time, 'T', 1), 'YYYY-MM-DD') as Page_updated_time_qvdate,

  //time#(subfield(updated_time, 'T', 2), 'hh:mm:ss+0000') as Page_updated_time_qvtime,

  page_id as Page_page_id,

  name as Page_name,

  //description as Page_description,

  is_published as Page_is_published,

  //picture as Page_picture,

  link as Page_link,

  category as Page_category,

  website as Page_website,

  username as Page_username,

  founded as Page_founded,

  //company_overview as Page_company_overview,

  //mission as Page_mission,

  //products as Page_products,

  can_post as Page_can_post,

  email as Page_email,

  privacy as Page_privacy,

  sum(likes) as Page_likes,

  sum(talking_about_count) as Page_talking_about_count,

  sum(checkins) as Page_checkins,

  sum(were_here_count) as Page_were_here_count

  FROM

  [http://localhost:5555/QVSource/FacebookFanPagesConnectorV2/?table=Page&FacebookPageOrId=$(page)&appI...)]

  (qvx)

  Group by Hash128(page,'-','$(vTodayAsDate)'),'$(vTodayAsDate)',page,page_id,name ,description ,is_published ,link,category,website,username,founded,company_overview,mission,

  products,can_post,email,privacy;

  next

if not isnull(size) then

  FB_Page_Summary_Table:

  LOAD Distinct *

  FROM $(vDestination)\$(fb_page_Summary) (qvd);

end if

  STORE FB_Page_Summary_Table INTO $(vDestination)\$(fb_page_Summary) (qvd);

//__________________________________________________________

//_____________________________________________________

temp_pagesID:  

load

  page_summary as page_temp,

  Page_page_id as page_id_temp,

  sum(Page_likes)

resident FB_Page_Summary_Table

group by page_summary, Page_page_id;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

let fb_page_feed = 'FB_Page_Feed_Table.qvd';

let size = filesize('$(vDestination)\$(fb_page_feed)');

let vIgnoreCache = 'true';

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

if vGetLatestData=1 then

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

   

  for i=0 to $(noRows)-1 // loop through every row

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

  let pageID = peek('page_id_temp', $(i), 'temp_pagesID');

       

     if(vIsFirstTime=1) then

      let loadUrl='$(vQVSourceBaseUrl)/QVSource/FacebookFanPagesConnectorV2/?table=FeedItems&FacebookPageOrId=$(page)

      &StartDate=$(vOldestFeedCreatedDateForInitialReload)&noCache=$(vIgnoreCache)&appID=$(vQVSourceAppId)';

     else

      let loadUrl='$(vQVSourceBaseUrl)/QVSource/FacebookFanPagesConnectorV2/?table=FeedItems&FacebookPageOrId=$(page)

      &StartDate=$(vOldestFeedCreatedDateForSubsequentReloads)&noCache=$(vIgnoreCache)&appID=$(vQVSourceAppId)';   

     endif

  FB_Page_Feed_Table_01:

  LOAD

  Hash128(page,'-',date(subfield(created, 'T', 1), 'DD/MM/YYYY') ) as Key_1,

  Hash128(page,'-',date(subfield(created, 'T', 1), 'DD/MM/YYYY'),'-',feeditem_id) as Key_2,

  page as page_feed,

  feeditem_id as feeditem_id,

  feeditem_id as Likers_Key,

  created as FeedItems_created,

  timestamp#(mid(created, 1, 10) & mid(created, 12, 8), 'DD/MM/YYYY hh:mm:ss') as FeedItems_created_qvdatetime,

  date(subfield(created, 'T', 1), 'DD/MM/YYYY') as FeedItems_created_qvdate,

  time#(subfield(created, 'T', 2), 'hh:mm:ss+0000') as FeedItems_created_qvtime,

  updated as FeedItems_updated,

  timestamp#(mid(updated, 1, 10) & mid(updated, 12, 8), 'DD/MM/YYYY hh:mm:ss') as FeedItems_updated_qvdatetime,

  date(subfield(updated, 'T', 1), 'DD/MM/YYYY') as FeedItems_updated_qvdate,

  time#(subfield(updated, 'T', 2), 'hh:mm:ss+0000') as FeedItems_updated_qvtime,

  poster_id as poster_id,

  if(poster_id = '$(pageID)', 'Yes', 'No') as FeedItem_is_self_posted,

  from_name as FeedItems_from_name,

  from_category as FeedItems_from_category,

  from_id as FeedItems_from_id,

  type as FeedItems_type,

  message as FeedItems_message,

  message_urlEncoded as FeedItems_message_urlEncoded,

  picture as FeedItems_picture,

  link as FeedItems_link,

  source as FeedItems_source,

  name as FeedItems_name,

  caption as FeedItems_caption,

  description as FeedItems_description,

  icon as FeedItems_icon,

  object_id as FeedItems_object_id,

  story as FeedItems_story,

  application_name as FeedItems_application_name,

  application_namespace as FeedItems_application_namespace,

  application_id as FeedItems_application_id,

  likes_count as FeedItems_likes_count,

  shares_count as FeedItems_shares_count,

  has_comments as FeedItems_has_comments,

  has_likes as FeedItems_has_lieks,

  comments_count as FeedItems_comments_count

  FROM

  [$(loadUrl)]

  (qvx);

  next

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  trace 'Establishing Feed in this RUN...';

  FB_Feed_this_Run:

  LOAD

  page_feed as FB_Feed_this_Run_Page,

  feeditem_id as FB_Feed_this_Run_id

  Resident FB_Page_Feed_Table_01;

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  trace 'Establishing number of feed items with comments to load in this run...';   // --- no CommentCount anymore in FeedTable

  FB_FeedItems_With_Comments:

  load

  page_feed as FB_FeedItemsLoadedInThisRun_page,

  feeditem_id as FB_FeedItemsLoadedInThisRun_id

  resident

  FB_Page_Feed_Table_01

  where FeedItems_has_comments = 'true';

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  trace 'Establishing number of feed items with Likes to load in this run...';

  FB_FeedItems_With_Likes:

  load

  page_feed as FB_FeedItemsLoadedInThisRun_page_with_Likes,

  feeditem_id as FB_FeedItemsLoadedInThisRun__With_Likes_id

  resident

  FB_Page_Feed_Table_01

  where FeedItems_has_lieks = 'true';

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

LET noRows = NoOfRows('FB_FeedItems_With_Comments');

  for i=0 to $(noRows)-1

  let feedID = peek('FB_FeedItemsLoadedInThisRun_id', $(i), 'FB_FeedItems_With_Comments');

  let page = peek('FB_FeedItemsLoadedInThisRun_page', $(i), 'FB_FeedItems_With_Comments');

FacebookFanPagesConnectorV2_CommentCountForItem:

LOAD

  '$(feedID)' as feeditem_id,

  total_count as CommentCountForItem_total_count,

  order as CommentCountForItem_order

FROM

[http://localhost:5555/QVSource/FacebookFanPagesConnectorV2/?table=CommentCountForItem&appID=&ObjectI...]

(qvx);

next

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

LET noRows = NoOfRows('FB_FeedItems_With_Likes');

  for i=0 to $(noRows)-1

  let feedID = peek('FB_FeedItemsLoadedInThisRun__With_Likes_id', $(i), 'FB_FeedItems_With_Likes');

  let page = peek('FB_FeedItemsLoadedInThisRun_page_with_Likes', $(i), 'FB_FeedItems_With_Likes');

FacebookFanPagesConnectorV2_LikeCountForItem:

LOAD

  '$(feedID)' as feeditem_id,

  total_count as LikeCountForItem_total_count

FROM

[http://localhost:5555/QVSource/FacebookFanPagesConnectorV2/?table=LikeCountForItem&appID=&ObjectId=$...)]

(qvx);

next

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

NoConcatenate

FB_Page_Feed_Table:

LOAD * Resident FB_Page_Feed_Table_01;

left Join LOAD * Resident FacebookFanPagesConnectorV2_CommentCountForItem;

Left Join LOAD * Resident FacebookFanPagesConnectorV2_LikeCountForItem;

//DROP Field FeedItems_likes_count ;

DROP Table FB_Page_Feed_Table_01;

DROP Table FacebookFanPagesConnectorV2_CommentCountForItem;

DROP Table FacebookFanPagesConnectorV2_LikeCountForItem;

/////////////////////////////////////////////////////////////////////////////////////////////////////

  if not isnull(size) then

  FB_Page_Feed_Table:

  LOAD * FROM $(vDestination)\$(fb_page_feed) (qvd)

  where not exists (feeditem_id);

  endif

  STORE FB_Page_Feed_Table INTO $(vDestination)\$(fb_page_feed) (qvd);

ELSE

  FB_Feed:

  LOAD * FROM $(vDestination)\$(fb_page_feed) (qvd);

endif

DROP Table temp_pagesID;