15 Replies Latest reply: Apr 20, 2012 9:15 AM by Aissam Boumejjane RSS

    problems with my load script

    Aissam Boumejjane

      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

          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

            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

              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
                  Aissam Boumejjane

                  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

                      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
                          Aissam Boumejjane

                          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

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

                                • Re: problems with my load script
                                  Aissam Boumejjane

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

                                  • Re: problems with my load script
                                    Aissam Boumejjane

                                    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

                                        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
                                            Aissam Boumejjane

                                            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

                                                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)

                                      • 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