0 Replies Latest reply: Jan 5, 2016 1:55 AM by tarek abd el latif RSS

    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)&appID=$(vQVSourceAppId)]

        (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=&ObjectId=$(feedID)&format=qvx]

      (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=$(feedID)]

      (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;