Multiple Load Statement

[marco_puccetti]

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

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

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

end if

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Thanks

Marco

[simospa]

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.

S.

[rubenmarin]

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

TableA:

LOAD Field From...;

TableB:

NoConcatenate Load Field From...;

[rubenmarin]

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

[simospa]

Hi Ruben,

mine is a trick, I agree with you 🙂

S.

[marco_puccetti]

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

[marco_puccetti]

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

[marco_puccetti]

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

[rubenmarin]

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!