topic Re: problems with my load script in QlikView

problems with my load script

Anonymous — Thu, 19 Apr 2012 13:46:07 GMT

Hi Guys,

I'm having problems with my script. The frist problem is that when I loaded my data (in debug mode) in my qlikview application, I get certain fields that are empty even though they are not. I've loaded more than 5000 lines, but the fields remain empty.

These Field remain empty:

INCOME,

Sales,

Client

KeyAccount

I don't know why, but I think it's related to the left join that I do and then load the data back using the RESIDENT function. Or there just something wrong with the way I load my script.

The second problem is that I can't do a reload. The moment I reload my data I get Execution of Script Failed.. It that because of my memory or CPU limitation/capacity? I'm 100% postive that the QVD file is not in use.

I've attached my script.

Please Help!

iSam

problems with my load script

Thu, 19 Apr 2012 14:04:56 GMT

Do you get results if you comment out the Resident Load section?

See if you are getting any data from the first section only. If you are not, see if you are getting data from only the second table. It could be an issue with the criteria of the tables.

problems with my load script

Thu, 19 Apr 2012 14:32:59 GMT

Hi iSam,

a left join in QV means to merge two tables via keyfields (they have same names in both tables) into one. It looks like these are in your case CLIENT and KeyAccount.

First:

I miss (and of course QV) Table "TempProCars". Due to that Qv will append the rows of Database.A "into" TempSystem.

Second:

if you would use "left join (TempSystem)":

In your Table TempSystem the corresponding (key-)field "CLIENT" is disabled, commented. So in this field can't be any value and therefor no single value of the CLIENT from Database.A will ever match.

Furthermore means a left join in QV data reduction. In a few words: take a row from feft table and see if you find a matching value in keyfield of right table. If not found, don't store it in result-table.

My suggestions would be (for testing pupose, best in in a new app):

Load some fields from TempSytsem including key fields. Duplicate each keyfield and give each a new name.

Same with Table Database.A.

Don't use any (Left) Join

Then Check your data (values) excespially in the keyfields and see how they fit.

quick and dirty like this:

LOAD

CLIENT,

KeyAccount

CLIENT_QVD,

KeyAccount_QVD

From Qvd

...

LOAD

CLIENT,

KeyAccount

CLIENT_Database,

KeyAccount_Database

SQL ...

HtH

Roland

P.S.:

Memory shouldn't be a problem with the script like above.

problems with my load script

Thu, 19 Apr 2012 14:37:26 GMT

Ahh yeah, I did not even notice that the Left Join was looking at (TempProCars). There isn't even a table with that name in your app. Did you mean to left join (TempSystem) ?

Re: problems with my load script

Anonymous — Thu, 19 Apr 2012 14:53:57 GMT

Hi Guys,

I Forgot to change TempProcars to TempSystem :S. My appologies for the confusion.

Just assume that all tables and fields are correct. Otherwise I would have received an error, regaring the a field or table name that can't be found

When I do a left join, and not a resident load, I get the all the fields displayed. It goes wrong the moment I do a resident load after the left join has been completed. Sales becomes zero, can't do a reload to load all the data. I've tried creating a new QV file, however the results are the same.

I've attached a new version in order to remove the confusion

Thanks in advance!

iSam

Re: problems with my load script

Thu, 19 Apr 2012 15:04:18 GMT

What happens when you remove :

Group BY

Shipments.Area,Shipments.DepCountry,Shipments.DisCountry,Shipments.ShipmentRef,VG,PROCARSCHARGES.ChargeAmountHome,Financial_Header_Ocean.VoucherType,

KeyMultiplier,KeyAccount,Shipments.shpmdate,Shipments.TEU,Shipments.shpmcwgt,Shipments.GrossWeight,CBM

From the TempSystem table?

Edit: Nevermind, I just saw the Max and Min functions. YOu could try moving them to the resident load and only do the group by there?

Re: problems with my load script

Anonymous — Thu, 19 Apr 2012 15:08:30 GMT

Hi Marc,

Yes I do get results when I comment out the Resident Load.

Regards,

iSam

Re: problems with my load script

Anonymous — Thu, 19 Apr 2012 15:20:31 GMT

I can’t do that because I’m using an aggregate function in the resident table.

If I delete it, I would get ‘invalid expression’;

Re: problems with my load script

Thu, 19 Apr 2012 15:35:20 GMT

Can you upload a sample with the resident section commented out?

problems with my load script

Thu, 19 Apr 2012 15:44:16 GMT

Hi Marc,

good Idea.

Hi iSam,

This is the next try:

INCOME is always zero

because sum(Financial_Charges_Ocean.ChargeAmountHome) is always zero

sum(Financial_Charges_Ocean.ChargeAmountHome)is always zero

because Financial_Charges_Ocean.ChargeAmountHome is always zero

same for Sales:

if(KeyMultiplier=KeyMultiplier is always true, and 0 *-1 is again 0)

HtH

Roland

Re: problems with my load script

Anonymous — Thu, 19 Apr 2012 16:21:39 GMT

I wil upload the data in an hour. connection at work is very slow

Re: problems with my load script

Anonymous — Thu, 19 Apr 2012 17:17:07 GMT

Hi Marc,

I've upload my file, without the resident load. As you can see the sales field is filled with data.

The moment I load from a resident, I get 0 in sales.

iSam

Re: problems with my load script

Thu, 19 Apr 2012 18:21:01 GMT

What is the error you are getting? I exported the data above and reduced it to 80k rows and tried doing the resident and it gave me an expression error, I changed the Group by section of the resident load and it gives no errors. From what I can see you are grouping the same in the resident load as in the main load? I might have missed something but if this is the case, it does not really make sense to do aggr in the resident as it is giving the same results as the main.

Re: problems with my load script

Anonymous — Fri, 20 Apr 2012 08:15:49 GMT

Hi marc,

If I do a group by in the resident load correctly I don’t get any error, the output of sales however remains zero.

The reason why I use group by twice is that I first aggregate the e.g. the maximum grossweight max(Shipments.GrossWeight) as MgrossWeight

Then I use the sum(MgrossWeight) in the resident table, since I’m using an aggregate function in the resident load I have to use the group by again.

I tried a different approach by using the Let statement, e.g.: Let vGrossWeight = max(Shipments.GrossWeight); and then call that variable in the resident load sum($(vGrossWeight)) as SumGrossWeight.

But that didn’t work also.

Please forgive me for my ignorance as I’m quit new to Qlikview and therefore I might do thinks which may seem inlogic.

Regards,

iSam

Re: problems with my load script

Fri, 20 Apr 2012 12:28:56 GMT

Can you look at the sample I posted and change the group by in the resident load to what you want? I was able to do it and return results in all fields, however I am not sure if it is the correct group by as the original was causing an invalid expression error. If you can change it to what you need and post the code for the resident according to this sample I can take another look at it.

Also, How many tables are joined to make the QVD? By looking at the sample I can not tell how the original data is put together and it appears to me that 2 tables with a one to many relationship were joined to cause duplicate data. This can cause issues with summaries as it will return incorrect results from duplicates.

I haven't used varibles in the script very much, but from what I understand they can only hold one value. With your group by is the original table, you are returning almost a million rows for shiments.grossweight so you can not set it to a variable based on your group by. I believe it would rewrite your vriable for every row it returns. (As I said I could be wrong, I do not use them often)

Re: problems with my load script

Anonymous — Fri, 20 Apr 2012 13:15:11 GMT

I marc,

I will create a protype (qv file + qvd's) and upload it. Hopefully that would give you more insight.

regards,

iSam