Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aboumejjane
Not applicable

problems with my load script

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

15 Replies
Not applicable

problems with my load script

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.

Not applicable

problems with my load script

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.

Not applicable

problems with my load script

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

aboumejjane
Not applicable

Re: problems with my load script

Hi Guys,

I Forgot to change TempProcars to TempSystem Smiley Frustrated. 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

Not applicable

Re: problems with my load script

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?

aboumejjane
Not applicable

Re: problems with my load script

Hi Marc,

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

Regards,

iSam

aboumejjane
Not applicable

Re: problems with my load script

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

Not applicable

Re: problems with my load script

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

Not applicable

problems with my load script

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