topic Re: Problem with Partial Load in QlikView

Problem with Partial Load

shane_spencer — Mon, 18 Jun 2012 14:54:26 GMT

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?

Re: Problem with Partial Load

Tue, 19 Jun 2012 07:51:29 GMT

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

Re: Problem with Partial Load

shane_spencer — Tue, 19 Jun 2012 08:43:50 GMT

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.

Re: Problem with Partial Load

Tue, 19 Jun 2012 13:41:24 GMT

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

Re: Problem with Partial Load

sebastiandperei — Tue, 19 Jun 2012 14:45:33 GMT

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;

Re: Problem with Partial Load

shane_spencer — Wed, 20 Jun 2012 08:01:19 GMT

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.

Re: Problem with Partial Load

sebastiandperei — Wed, 20 Jun 2012 13:52:19 GMT

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.

Re: Problem with Partial Load

shane_spencer — Wed, 20 Jun 2012 14:51:32 GMT

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!

Re: Problem with Partial Load

sebastiandperei — Wed, 20 Jun 2012 14:57:50 GMT

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

Re: Problem with Partial Load

shane_spencer — Thu, 21 Jun 2012 12:40:34 GMT

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! :]

Re: Problem with Partial Load

sebastiandperei — Thu, 21 Jun 2012 13:53:22 GMT

Shane, I saw the app you sent.

The result of using Keep is showing that there aren't matching values between data and Server tables. Keep will only "keeps" the coincidences between both tables in the common field, and if you haven't any table (like your last app sended), is the reason that the field "Server" (common in both tables) hasn't any common value in both tables.

Check the "Server" column in GFA Utilisation Reporting Summary.xls and compare it with the values in your partial load. Is very extrange that these values in QV are empty.... Tell me, what kind of values do you have in this excel file? And what kind in *PerfMon*,qvd?

Re: Problem with Partial Load

shane_spencer — Thu, 21 Jun 2012 14:12:11 GMT

The excel files has details about the server, Role, Application etc. I only want the data from that file if the Server exists in the "data" table.

HK000TKO4050_20120602.csv

is an example of the raw data I'm collecting

(n.b. I've got rid of the "Generate a report detailing the..." comment from future logs)

GSRS_Perfmon_processing.qvw

is the script I'm using for processing the hundreds of CSV files we have.

Part_processed_GSRS_201206.qvd

Is all these CSV's just loaded in to a single QVD for the month.

GSRS_Perfmon_201206.qvd

Is the processed "data" that gets loaded in to my Report/App (attached previously). As you can see there's some junk in there as the "date" somehow becomes a submetric which I think is where the blanks crop up in the Server column.

GFA Utilisation Reporting Summary.xls

Is the spreadsheet that contains further data about the servers that I link in my Report/App(attached previously).

...I can see the method I'm using isn't perfect but it's about the best I can do. 😕

Re: Problem with Partial Load

sebastiandperei — Thu, 21 Jun 2012 18:15:37 GMT

Yes, the Submetric "date" is a problem. You can exclude these registers by Where sub_Metric<>'date'; at the load.

Also, like this way, you will get the field "value" with the right format (now, is date format, because of this entry in sub_Metric)

Re: Problem with Partial Load

shane_spencer — Fri, 22 Jun 2012 09:43:11 GMT

Thanx! I had actually used something similar:

Where Len(Server)>0;

as I seem to have got some other junk data in some of my source files. But if I can get that cleaned up I can see that removing the 'data' from 'submetric' earlier on in the Processing would be a good idea.

Re: Problem with Partial Load

sebastiandperei — Fri, 22 Jun 2012 13:25:26 GMT

I think your idea is better!!!