qlikview facebook application conversion to V2 con... - Qlik Community

Report Inappropriate Content · ‎2016-01-05

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;

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

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

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

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;

qlikview facebook application conversion to V2 connector error