Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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;

Community Browser