Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Problem with Partial Load

I'm set up a Partial reload to add new data in to Memory each day, however the new data is not linked to a seperate table like the existing data is:

for each File in FileList('*PerfMon_$(yestermonth).qvd')

//Only selects QVD Files to load data from if they're from yesterdays month

data: ADD Only LOAD Date,

     Server,

     Hour,

     Minute,

     Main_metric,

     sub_Metric,

     value

FROM '$(File)' (qvd)

WHERE Date='$(yesterdate)';

//Loads date only where the Date is yesterdays

next File

...this should be linked by Server to another table but the Partially Loaded data is not. Can any help?

1 Solution

Accepted Solutions
sebastiandperei
Specialist
Specialist

Dear Shane,

Take a Look at "Sheet2".

There, in your table, I added the fields that you Join in AOB. The "Server" Field, is the common key that you need to make correspondence between both tables.

The files with empty server field (they exists!! as you can see) don't have the look of be the joined in AOB, because they didn't have information in ARN, Class, Role or Service.

As you see, there are two servers, and every data you join in AOB, multiplies by these two servers and, of course, one more time, by empty server, the content of Server field in the partial load (or from excel readed in AOB) This is why the reload is taking more time that it must.

This happens when Join is "mixing" instead of "joining". I recomend you analize your sources. Reload with only the two tables that you will join in separate instances of QV, and then compare if you have the same values in "Server" field.

View solution in original post

14 Replies
Not applicable

Hi Shane,

did you try to execute that loop with normal reload?

It seems that loop is empty because qvds names are not created as expected. Try with this in the first line: for each File in FileList('*PerfMon_'&'$(yestermonth)'&'.qvd')

regards

shane_spencer
Specialist
Specialist
Author

The QVD's I'm loading do exist and are created by seperate scripts. The Full and Partial Load both find the QVD's and upload the data, however the Partial Load does not link the new data to another table.

In the AOB tab there's a Join to a spreadsheet that contains more details about the Server(s), but the new data on Partial Reload does not get Joined to this table for some reason.

Not applicable

I would suggest to trace partial reload activities, to be sure that partial reload was called and what file names were read during execution; in the main tab you can replace second loop with this one and check log files after reload:

if IsPartialReload() then

TRACE "Partial reload called...";

for each File in FileList('*PerfMon_$(yestermonth).qvd')

TRACE "File name read: " & $(File)

//Only selects QVD Files to load data from if they're from yesterdays month

data: ADD Only LOAD Date,

     Server,

     Hour,

     Minute,

     Main_metric,

     sub_Metric,

     value

FROM '$(File)' (qvd)

WHERE Date='$(yesterdate)';

//Loads date only where the Date is yesterdays

next File

end if

sebastiandperei
Specialist
Specialist

Hi Shane,

I was seeing your script and data stored. If you do a simple table with all the fields in "data" table, you can see that the new values has the "Server" field empty. And this is the reason of they can't be linked with Join.

Test a simple load that reads one of these QVD (yesterday's ones), and try to find with table explorer why this field is empty. Or add this sentence at start of script and reload it, and send it again.

Test:

Load *

From PerfMon_201205.qvd;   <- or one exact filename.

exit script;

shane_spencer
Specialist
Specialist
Author

I tried both those suggestions and I can see that the new data is loading, it's simply not joining to the other tables. I'm not sure what you mean about the Server field being empty Sebastian as it does contain the servername on both my load and partial load data.

edit: Iit turns out the Partial Load is not working as I expected, instead it's loading all data instead of just the date specified by the WHERE command.

Given that the full LOAD of 1 1/2 months of data is only taking 4 minutes on my server (much less than I expected from testing on my laptop) I think I'm going to abandon the Partial Load idea altogether.

sebastiandperei
Specialist
Specialist

Dear Shane,

Take a Look at "Sheet2".

There, in your table, I added the fields that you Join in AOB. The "Server" Field, is the common key that you need to make correspondence between both tables.

The files with empty server field (they exists!! as you can see) don't have the look of be the joined in AOB, because they didn't have information in ARN, Class, Role or Service.

As you see, there are two servers, and every data you join in AOB, multiplies by these two servers and, of course, one more time, by empty server, the content of Server field in the partial load (or from excel readed in AOB) This is why the reload is taking more time that it must.

This happens when Join is "mixing" instead of "joining". I recomend you analize your sources. Reload with only the two tables that you will join in separate instances of QV, and then compare if you have the same values in "Server" field.

shane_spencer
Specialist
Specialist
Author

Thank you, I understand now. It seems there is an underlying problem I did not even realise was there. I must admit I know very little about joining tables, in fact his part of the work was done by a colleage then handed over to me so I guess I better start from scratch on this. Cheers!

sebastiandperei
Specialist
Specialist

Don´t exaggerate!!!

I have post in other of your question that what not resolved... I see you didn't mark as "Correct Answer" any post!!!

Make sure your resolved posts are maked like that.

Regards

shane_spencer
Specialist
Specialist
Author

It seems the problem was not so much with the Partial Load but with how the tables were linked, an underlying problem I did not notice.

I have used a KEEP command rather than a JOIN which seems to keep the tables seperate so is better suited to my purposes.

Server: Left Keep (data) LOAD Server,

     Service,

     ARN,

     Class,

     Role,

     [Data Collector],

     [Log File Location]

FROM

[\\hbeu.adroot.hsbc\dfsroot\GB002\CBI_SUPP_ENV\Performance Docs\GFA Utilisation Reporting Summary.xls]

(biff, embedded labels, table is Servers$);

I also prevented the NULL data from being loaded using the WHERE command

Where Len(Server)>0;

I am still unsure as to whether I will use Partial Load as a full load of nearly two months of data just took 3 1/2 minutes, and older data I think I will average up for the Hour rather than the Five Minute Interval, but the help is much appreciated!

n.b. Now I've been able to test and apply your suggestions to resolve the issue I've marked it as Correct. Cheers! :]