topic Re: LOAD statement in QlikView

LOAD statement

marco_puccetti — Wed, 27 May 2015 08:50:31 GMT

Hello, i've noticed that when i make a load of some datas, is generated the qvd (with store command) but the table in-memory is not created, is it possible?

Thanks

Marco

Re: LOAD statement

petter — Wed, 27 May 2015 08:56:10 GMT

Not unless the table is dropped or QlikView finds out that what you are loading from the QVD can be auto concatenated. The last option is that you force concatenate the QVD - which you would obviously know about by using CONCATENATE in front of the LOAD statement.

Re: LOAD statement

petter — Wed, 27 May 2015 08:57:10 GMT

By the way ... a mapping load table will vanish by it self at the completion of the load script.

Re: LOAD statement

alexandros17 — Wed, 27 May 2015 08:58:06 GMT

No it is not possible, you have to find the table in memory, plase share with us you script

Re: LOAD statement

petter — Wed, 27 May 2015 09:00:34 GMT

There is a statement called DROP that can drop one or more tables explicitly:

DROP TABLE TempTable;

DROP TABLES TempDates1, TempDates2;

Re: LOAD statement

marco_puccetti — Wed, 27 May 2015 09:02:20 GMT

The code is like this:

TAB:

LOAD

Field 1 as A,

Field 2 as B,

........,

Field n as Z

FROM URL; (qvx)

Store TAB Into '$(pathToFile)';

And i get the qvd file, but not the table TAB in the Tables Visualizer.

Thanks

Marco

Re: LOAD statement

marco_puccetti — Wed, 27 May 2015 09:04:31 GMT

The exact code is this:

'****************************************************************************

let inst_pro_media = '$(vQvdPath)\INST_Media.qvd';

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

if not isnull(size) then

INST_Media:

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

end if

if(vGetLatestData=1) then

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

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

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

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

INST_Media:

LOAD

id as MediaByTag_id,

min_tag_id as MediaByTag_min_tag_id,

max_tag_id as MediaByTag_max_tag_id,

type as MediaByTag_type,

tags as MediaByTag_tags,

location_id as MediaByTag_location_id,

location_name as MediaByTag_location_name,

location_latitude as MediaByTag_location_latitude,

location_longitude as MediaByTag_location_longitude,

filter as MediaByTag_filter,

link as MediaByTag_link,

created_time as MediaByTag_created_time,

created_time_utc as MediaByTag_created_time_utc,

timestamp#(created_time_utc, 'DD-MM-YYYY hh:mm:ss') as MediaByTag_created_time_utc_qlik_timestamp,

date#(left(created_time_utc, 10), 'DD-MM-YYYY') as MediaByTag_created_time_utc_qlik_date,

comments_count as MediaByTag_comments_count,

likes_count as MediaByTag_likes_count,

caption_created_time as MediaByTag_caption_created_time,

caption_created_time_utc as MediaByTag_caption_created_time_utc,

timestamp#(caption_created_time_utc, 'DD-MM-YYYY hh:mm:ss') as MediaByTag_caption_created_time_utc_qlik_timestamp,

date#(left(caption_created_time_utc, 10), 'DD-MM-YYYY') as MediaByTag_caption_created_time_utc_qlik_date,

caption_text as MediaByTag_caption_text,

caption_from_username as MediaByTag_caption_from_username,

caption_from_profile_picture as MediaByTag_caption_from_profile_picture,

caption_from_id as MediaByTag_caption_from_id,

caption_from_full_name as MediaByTag_caption_from_full_name,

caption_id as MediaByTag_caption_id,

images_low_resolution_url as MediaByTag_images_low_resolution_url,

images_low_resolution_width as MediaByTag_images_low_resolution_width,

images_low_resolution_height as MediaByTag_images_low_resolution_height,

images_thumbnail_url as MediaByTag_images_thumbnail_url,

images_thumbnail_width as MediaByTag_images_thumbnail_width,

images_thumbnail_height as MediaByTag_images_thumbnail_height,

images_standard_resolution_url as MediaByTag_images_standard_resolution_url,

images_standard_resolution_width as MediaByTag_images_standard_resolution_width,

images_standard_resolution_height as MediaByTag_images_standard_resolution_height,

videos_low_bandwidth_url as MediaByTag_videos_low_bandwidth_url,

videos_low_bandwidth_width as MediaByTag_videos_low_bandwidth_width,

videos_low_bandwidth_height as MediaByTag_videos_low_bandwidth_height,

videos_standard_resolution_url as MediaByTag_videos_standard_resolution_url,

videos_standard_resolution_width as MediaByTag_videos_standard_resolution_width,

videos_standard_resolution_height as MediaByTag_videos_standard_resolution_height,

videos_low_resolution_url as MediaByTag_videos_low_resolution_url,

videos_low_resolution_width as MediaByTag_videos_low_resolution_width,

videos_low_resolution_height as MediaByTag_videos_low_resolution_height,

user_has_liked as MediaByTag_user_has_liked,

user_username as MediaByTag_user_username,

user_profile_picture as MediaByTag_user_profile_picture,

user_full_name as MediaByTag_user_full_name,

user_id as MediaByTag_user_id,

users_in_photo_count as MediaByTag_users_in_photo_count,

users_in_photo_user_ids as MediaByTag_users_in_photo_user_ids

FROM

[$(vQVSourceBaseUrl)/QVSource/InstagramConnector/?table=MediaByTag&appID=$(vQVSourceAppId)&tag=$(site)](qvx);

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

trace "Tw Follower Url: " & $(vQVSourceBaseUrl)/QVSource/InstagramConnector/?table=MediaByTag&appID=$(vQVSourceAppId)&tag=$(site);

next

STORE INST_Media INTO '$(inst_pro_media)';

end if

'****************************************************************************

Re: LOAD statement

alexandros17 — Wed, 27 May 2015 09:05:36 GMT

Are you sure that the qvd is filled with values?

I think that you are creating an empty file (.qvd) starting from a table with no rows (this coult be the reason for which the table is not shown)

Let me know

Re: LOAD statement

petter — Wed, 27 May 2015 09:16:14 GMT

The only reason you could have for not getting a table in-memory is that the QVD you are loading from is empty or does not exist and the QVX's that the FOR NEXT loop is retrieving from does not have data or they don't exist. There is a test also against a variable vGetLatestData which it will act upon to either fetch new data or not.

Whether the script reports the errors or not depends on your error reporting setting - which you can change with the variable ErrorMode.

Re: LOAD statement

marco_puccetti — Wed, 27 May 2015 09:34:16 GMT

I have tried to load the qvd and it presents data inside.

What's this ErrorMode variable, and how should be setted?

Marco

Re: LOAD statement

marco_puccetti — Wed, 27 May 2015 09:43:34 GMT

Another question is related to this statement:

let noRows = NoOfRows('InstagramSites'), this statement returns NULL (I have replaced the Instagram Sites in order to let it work).

The table is defined in a config file located like this:

$(Must_include=C:\Users\m.puccetti\Desktop\LAVORO\QLIKVIEW\DEMO\Social Media Analyzer\SETTINGS\config.txt);

And within the config, the table is defined like this:

// Instagram parameters

InstagramSites:

LOAD * INLINE [

tpage, tSite

999999999999999, Bancadinamica

];

I have debugged the script and i've seen that the config is linked to the code, so it should work.

Any ideas?

Marco

Re: LOAD statement

marco_puccetti — Wed, 27 May 2015 10:01:28 GMT

Now the table is loaded thanks to the qvd creation.

Marco

Re: LOAD statement

petter — Wed, 27 May 2015 10:04:02 GMT

QlikView Reference Manual (English) PDF:

Page 289 - Chapter 24 - Script Syntax has two or more pages of explanation and example of the use of ErrorMode and ScriptError.

Re: LOAD statement

petter — Wed, 27 May 2015 10:05:07 GMT

When you get a Null-value from NoOfRows(<tablename>) then there is no <tablename> table to count the rows in.... If the table exists then it would give 0 or more as a value.

Re: LOAD statement

timanshu — Wed, 27 May 2015 10:46:33 GMT

Kindly mark most helpful reply as answer. It will help others to find the answer of what they look for.

Re: LOAD statement

marco_puccetti — Wed, 27 May 2015 10:54:32 GMT

The table is created in the config file, but it seems not to be included in the script.

Marco