topic Re: Multiple Load Statement in QlikView

Multiple Load Statement

marco_puccetti — Wed, 27 May 2015 14:32:04 GMT

Hello i have generated this code, that should create a merged table; executing the script all works but the final table contains only the first table's datas.

See code below:

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

let youtube_pro_media = '$(vQvdPath)\YOUTUBE_Media.qvd';

let size = filesize('$(youtube_pro_media)');

if not isnull(size) then

YOUTUBE_Media:

LOAD * FROM '$(youtube_pro_media)' (qvd);

end if

if(vGetLatestData=1) then

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

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

let site = peek('tSite', $(i), 'TwitterSites'); // get the value for "site" field on each row

let page = peek('tpage', $(i), 'TwitterSites'); // get the value for facebook page

YOUTUBE_Media:

LOAD

kind as VideoSearch_kind,

videoId as VideoSearch_videoId,

videoLink as VideoSearch_videoLink,

publishedAt as VideoSearch_publishedAt,

timestamp#(left(publishedAt, 10) & ' ' & mid(publishedAt, 12, 8), 'YYYY-MM-DD hh:mm:ss') as VideoSearch_publishedAt_qlik_timestamp,

date#(left(publishedAt, 10), 'YYYY-MM-DD') as VideoSearch_publishedAt_qlik_date,

channelId as VideoSearch_channelId,

channelLink as VideoSearch_channelLink,

channelTitle as VideoSearch_channelTitle,

title as VideoSearch_title,

description as VideoSearch_description,

thumbnailDefaultUrl as VideoSearch_thumbnailDefaultUrl,

liveBroadcastContent as VideoSearch_liveBroadcastContent

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoSearch&appID=$(vQVSourceAppId)&query=$(site)](qvx);

//call logQVSourceError(scripterrordetails, 'Instagram\Page\$(site)');

trace "YOUTUBE_Media: " & $(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoSearch&appID=$(vQVSourceAppId)&query=$(site);

//*****************************************************

//Get ExTRA info for each video and create final table

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

for i=0 to $(noRows)-1

let kind = peek('VideoSearch_kind',$(i),'YOUTUBE_Media');

let videoId = peek('VideoSearch_videoId',$(i),'YOUTUBE_Media');

let videoLink = peek('VideoSearch_videoLink',$(i),'YOUTUBE_Media');

let publishedAt = peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media');

let timestamp = timestamp#(left(peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media'), 10) & ' ' & mid(peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media'), 12, 8), 'YYYY-MM-DD hh:mm:ss');

let qlikdate = date#(left(peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media'), 10), 'YYYY-MM-DD');

let channelId = peek('VideoSearch_channelId',$(i),'YOUTUBE_Media');;

let channelLink = peek('VideoSearch_channelLink',$(i),'YOUTUBE_Media');

let channelTitle = peek('VideoSearch_channelTitle',$(i),'YOUTUBE_Media');

let title = peek('VideoSearch_title',$(i),'YOUTUBE_Media');

let description = peek('VideoSearch_description',$(i),'YOUTUBE_Media');

let thumbnailDefaultUrl = peek('VideoSearch_thumbnailDefaultUrl',$(i),'YOUTUBE_Media');

let liveBroadcastContent = peek('VideoSearch_liveBroadcastContent',$(i),'YOUTUBE_Media');;

YOUTUBE_Media_tmp:

LOAD

'$(kind)' as VideoSearch_kind,

'$(videoId)' as VideoSearch_videoId,

'$(videoLink)' as VideoSearch_videoLink,

'$(publishedAt)' as VideoSearch_publishedAt,

'$(timestamp)' as VideoSearch_publishedAt_qlik_timestamp,

'$(qlikdate)' as VideoSearch_publishedAt_qlik_date,

'$(channelId)' as VideoSearch_channelId,

'$(channelLink)' as VideoSearch_channelLink,

'$(channelTitle)' as VideoSearch_channelTitle,

'$(title)' as VideoSearch_title,

'$(description)' as VideoSearch_description,

'$(thumbnailDefaultUrl)' as VideoSearch_thumbnailDefaultUrl,

'$(liveBroadcastContent)' as VideoSearch_liveBroadcastContent,

id as VideoStatistics_id,//EXTRA DATA

title as VideoStatistics_title,

description as VideoStatistics_description,

viewCount as VideoStatistics_viewCount,

likeCount as VideoStatistics_likeCount,

dislikeCount as VideoStatistics_dislikeCount,

favoriteCount as VideoStatistics_favoriteCount,

commentCount as VideoStatistics_commentCount

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoStatistics&appID=&videoIds=$(videoId)] (qvx);

trace "YOUTUBE_Media_tmp: " & $(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoStatistics&appID=&videoIds=$(videoId);

//*****************************************************

YOUTUBE_Media:

LOAD * RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

STORE YOUTUBE_Media INTO '$(youtube_pro_media)';

end if

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Thanks

Marco

Re: Multiple Load Statement

simospa — Wed, 27 May 2015 14:45:49 GMT

Hi Marco,

try to use a fake field in

YOUTUBE_Media:

LOAD * RESIDENT YOUTUBE_Media_tmp;

in this way:

YOUTUBE_Media:

LOAD *, 'x' as FakeX RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

DROP FIELD FakeX;

In this way you are creating a new table: QlikView concatenate in a single entity the tables with the same structure. To have a new table you have to use a filed that "breaks" the previous structure and let QlikView think that you are creating a very new table.

Let me know.

Re: Multiple Load Statement

rubenmarin — Wed, 27 May 2015 15:03:53 GMT

Hi Simone, you can use 'NoConcatenate' to avoid auto-concatenating tables, ie:

TableA:

LOAD Field From...;

TableB:

NoConcatenate Load Field From...;

Re: Multiple Load Statement

rubenmarin — Wed, 27 May 2015 15:05:09 GMT

Hi Marco, load your last table this way to force QV to add the records of YOUTUBE_Media_tmp to YOUTUBE_Media:

Concatenate (YOUTUBE_Media)

LOAD * RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

STORE YOUTUBE_Media INTO '$(youtube_pro_media)';

Re: Multiple Load Statement

simospa — Wed, 27 May 2015 15:06:35 GMT

Hi Ruben,

mine is a trick, I agree with you 🙂

Re: Multiple Load Statement

marco_puccetti — Thu, 28 May 2015 08:11:01 GMT

So the other code is ok?

After the for cicle i have to add this:

Concatenate (YOUTUBE_Media)

LOAD * RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

Isn't it?

Marco

Re: Multiple Load Statement

marco_puccetti — Thu, 28 May 2015 08:29:41 GMT

Using the concatenate generate a sort of slice of table and is not properly correct, so i ask you if with this form the table is properly structured:

LOAD

'$(kind)' as VideoSearch_kind,

'$(videoId)' as VideoSearch_videoId,

'$(videoLink)' as VideoSearch_videoLink,

'$(publishedAt)' as VideoSearch_publishedAt,

'$(timestamp)' as VideoSearch_publishedAt_qlik_timestamp,

'$(qlikdate)' as VideoSearch_publishedAt_qlik_date,

'$(channelId)' as VideoSearch_channelId,

'$(channelLink)' as VideoSearch_channelLink,

'$(channelTitle)' as VideoSearch_channelTitle,

'$(title)' as VideoSearch_title,

'$(description)' as VideoSearch_description,

'$(thumbnailDefaultUrl)' as VideoSearch_thumbnailDefaultUrl,

'$(liveBroadcastContent)' as VideoSearch_liveBroadcastContent,

id as VideoStatistics_id,//EXTRA DATA

title as VideoStatistics_title,

description as VideoStatistics_description,

viewCount as VideoStatistics_viewCount,

likeCount as VideoStatistics_likeCount,

dislikeCount as VideoStatistics_dislikeCount,

favoriteCount as VideoStatistics_favoriteCount,

commentCount as VideoStatistics_commentCount

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoStatistics&appID=&videoIds=$(videoId)] (qvx);

Have i to use * instead of the latest 8 fields?

Like this

LOAD

'$(kind)' as VideoSearch_kind,

'$(videoId)' as VideoSearch_videoId,

'$(videoLink)' as VideoSearch_videoLink,

'$(publishedAt)' as VideoSearch_publishedAt,

'$(timestamp)' as VideoSearch_publishedAt_qlik_timestamp,

'$(qlikdate)' as VideoSearch_publishedAt_qlik_date,

'$(channelId)' as VideoSearch_channelId,

'$(channelLink)' as VideoSearch_channelLink,

'$(channelTitle)' as VideoSearch_channelTitle,

'$(title)' as VideoSearch_title,

'$(description)' as VideoSearch_description,

'$(thumbnailDefaultUrl)' as VideoSearch_thumbnailDefaultUrl,

'$(liveBroadcastContent)' as VideoSearch_liveBroadcastContent,

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoStatistics&appID=&videoIds=$(videoId)] (qvx);

Marco

Re: Multiple Load Statement

marco_puccetti — Thu, 28 May 2015 08:39:46 GMT

Hello anybody, what's wrong in this statement?

I need to obtain a table with the first data of the first table and the last from the connector qvsource.

YOUTUBE_Media:

LOAD

kind as VideoSearch_kind,

videoId as VideoSearch_videoId,

videoLink as VideoSearch_videoLink,

publishedAt as VideoSearch_publishedAt,

timestamp#(left(publishedAt, 10) & ' ' & mid(publishedAt, 12, 8), 'YYYY-MM-DD hh:mm:ss') as VideoSearch_publishedAt_qlik_timestamp,

date#(left(publishedAt, 10), 'YYYY-MM-DD') as VideoSearch_publishedAt_qlik_date,

channelId as VideoSearch_channelId,

channelLink as VideoSearch_channelLink,

channelTitle as VideoSearch_channelTitle,

title as VideoSearch_title,

description as VideoSearch_description,

thumbnailDefaultUrl as VideoSearch_thumbnailDefaultUrl,

liveBroadcastContent as VideoSearch_liveBroadcastContent

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoSearch&appID=$(vQVSourceAppId)&query=$(site)](qvx);

//call logQVSourceError(scripterrordetails, 'Instagram\Page\$(site)');

trace "YOUTUBE_Media: " & $(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoSearch&appID=$(vQVSourceAppId)&query=$(site);

next

//*****************************************************

//Get ExTRA info for each video and create final table

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

for i=0 to $(noRows)-1

let kind = peek('VideoSearch_kind',$(i),'YOUTUBE_Media');

let videoId = peek('VideoSearch_videoId',$(i),'YOUTUBE_Media');

let videoLink = peek('VideoSearch_videoLink',$(i),'YOUTUBE_Media');

let publishedAt = peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media');

let timestamp = timestamp#(left(peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media'), 10) & ' ' & mid(peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media'), 12, 8), 'YYYY-MM-DD hh:mm:ss');

let qlikdate = date#(left(peek('VideoSearch_publishedAt',$(i),'YOUTUBE_Media'), 10), 'YYYY-MM-DD');

let channelId = peek('VideoSearch_channelId',$(i),'YOUTUBE_Media');;

let channelLink = peek('VideoSearch_channelLink',$(i),'YOUTUBE_Media');

let channelTitle = peek('VideoSearch_channelTitle',$(i),'YOUTUBE_Media');

let title = peek('VideoSearch_title',$(i),'YOUTUBE_Media');

let description = peek('VideoSearch_description',$(i),'YOUTUBE_Media');

let thumbnailDefaultUrl = peek('VideoSearch_thumbnailDefaultUrl',$(i),'YOUTUBE_Media');

let liveBroadcastContent = peek('VideoSearch_liveBroadcastContent',$(i),'YOUTUBE_Media');

YOUTUBE_Media_tmp:

LOAD

'$(kind)' as VideoSearch_kind,

'$(videoId)' as VideoSearch_videoId,

'$(videoLink)' as VideoSearch_videoLink,

'$(publishedAt)' as VideoSearch_publishedAt,

'$(timestamp)' as VideoSearch_publishedAt_qlik_timestamp,

'$(qlikdate)' as VideoSearch_publishedAt_qlik_date,

'$(channelId)' as VideoSearch_channelId,

'$(channelLink)' as VideoSearch_channelLink,

'$(channelTitle)' as VideoSearch_channelTitle,

'$(title)' as VideoSearch_title,

'$(description)' as VideoSearch_description,

'$(thumbnailDefaultUrl)' as VideoSearch_thumbnailDefaultUrl,

'$(liveBroadcastContent)' as VideoSearch_liveBroadcastContent,

id as VideoStatistics_id,//EXTRA DATA

title as VideoStatistics_title,

description as VideoStatistics_description,

viewCount as VideoStatistics_viewCount,

likeCount as VideoStatistics_likeCount,

dislikeCount as VideoStatistics_dislikeCount,

favoriteCount as VideoStatistics_favoriteCount,

commentCount as VideoStatistics_commentCount

FROM

[$(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoStatistics&appID=&videoIds=$(videoId)] (qvx);

trace "YOUTUBE_Media_tmp: " & $(vQVSourceBaseUrl)/QVSource/YouTubeConnectorV3/?table=VideoStatistics&appID=&videoIds=$(videoId);

next

//*****************************************************

YOUTUBE_Media:

LOAD * RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

STORE YOUTUBE_Media INTO '$(youtube_pro_media)';

Re: Multiple Load Statement

rubenmarin — Thu, 28 May 2015 08:52:19 GMT

Hi Marco, your code:

YOUTUBE_Media:

LOAD * RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

As Simone said, most probably this code is autoconcatenated to YOUTUBE_Media_tmp, and then the table is dropped, so none of the records was added to YOUTUBE_Media.

I think you're trying to add the records from YOUTUBE_Media_tmp to the previously loaded table YOUTUBE_Media, if this is what you want, you can change that code to:

Concatenate (YOUTUBE_Media)

LOAD * RESIDENT YOUTUBE_Media_tmp;

DROP TABLE YOUTUBE_Media_tmp;

Concatenate (YOUTUBE_Media) --> This tells QV to add the next records to the YOUTUBE_Media table

LOAD * RESIDENT YOUTUBE_Media_tmp; --> The records you want to add

DROP TABLE YOUTUBE_Media_tmp; --> Deletes the table with the temp records already added

Hope this helps!