14 Replies Latest reply: Sep 6, 2011 6:54 AM by jenark81 RSS

    Reloading a cross tab field in twice

      Hi,

       

      I'm loading in multiple spreadsheet all in a crosstab format, which is working fine, but when I want to do an expression on the  'Data' (typically 'Sum (Data)' in a chart)  it is coming up with with the error message 'You have attempted to apply a numerical expression, which is not uniquely defined for the specified variable. In order to use this variable in expressions relying on number of occurences, please read it a second time under a new name from its primary table'

       

      I understand from this I need to load 'Data' in twice, as Data has formed a key, and that means it won't calculate, but as its not actually source in the primary table, I'm not sure how to do it. I've tried putting in the crosstab field twice (e.g. in red in the code below) but in that instance only Datacalc loaded. I can't Load Data from the source data as it doesn't work.

       

      I would welcome any ideas!

       

       

                CrossTable(Funding, Data, 7)
                Cross Table(Funding, Datacalc, 7)
                LOAD Year,
           [Cost Centre], 
           [Cost Centre Number], 
           INSTID, 
           UKPRN, 
           [Region of institution], 
           Institution, 
           RCUK, 
           [UK Charities], 
           [UK Charities other], 
           [UK Gvt], 
           [UK Industry], 
           [EU Gvt], 
           [EU charity], 
           [EU industry], 
           [EU other], 
           [Non EU charities], 
           [Non EU industry], 
           [Non Eu Other], 
           Other, 
           Total,
           Data as [Source of Funding] 
      
      
        • Re: Reloading a cross tab field in twice
          Shaun Jansen Van Nieuwenhuizen

          try the following:

           

          temp:

          CrossTable(Funding, Data, 7)
                    Cross Table(Funding, Datacalc, 7)
                    LOAD Year,
               [Cost Centre],
               [Cost Centre Number],
               INSTID,
               UKPRN,
               [Region of institution],
               Institution,
               RCUK,
               [UK Charities],
               [UK Charities other],
               [UK Gvt],
               [UK Industry],
               [EU Gvt],
               [EU charity],
               [EU industry],
               [EU other],
               [Non EU charities],
               [Non EU industry],
               [Non Eu Other],
               Other,
               Total,
               Data as [Source of Funding]

           

           

          table1:

          noconcatenate

          load

          [Cost Centre],

               [Cost Centre Number],

               INSTID,

               UKPRN,

               [Region of institution],

               Institution,

               RCUK,

               [UK Charities],

               [UK Charities other],

               [UK Gvt],

               [UK Industry],

               [EU Gvt],

               [EU charity],

               [EU industry],

               [EU other],

               [Non EU charities],

               [Non EU industry],

               [Non Eu Other],

               Other,

               Total,

          [Source of Funding]

          resident temp;

          drop table temp;

          • Re: Reloading a cross tab field in twice
            Shaun Jansen Van Nieuwenhuizen

            also note that the code you supplied above is not complete, the one I supplied is only a example, only copy the last code, from "table1" to "drop table temp;", and add "temp:" above your first load

            • Reloading a cross tab field in twice

              Thanks for replying. I've tried loading that in but I still get Datacalc as a key field. 

               

              For information, the whole of my original code is as follows:

               

              for each vSheet in 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Sheet17', 'Sheet18', 'Sheet19', 'Sheet20', 'Sheet21', 'Sheet22', 'Sheet23', 'Sheet25', 'Sheet27', 'Sheet28', 'Sheet29', 'Sheet30', 'Sheet31', 'Sheet32', 'Sheet33', 'Sheet34', 'Sheet35', 'Sheet36', 'Sheet37', 'Sheet38'

                        CrossTable(Funding, Data, 7)

                        CrossTable(Funding, Datacalc, 7)

                        LOAD Year,

                   [Cost Centre],

                   [Cost Centre Number],

                   INSTID,

                   UKPRN,

                   [Region of institution],

                   Institution,

                   RCUK,

                   [UK Charities],

                   [UK Charities other],

                   [UK Gvt],

                   [UK Industry],

                   [EU Gvt],

                   [EU charity],

                   [EU industry],

                   [EU other],

                   [Non EU charities],

                   [Non EU industry],

                   [Non Eu Other],

                   Other,

                   Total

              FROM

              [J:\NAMEOFFILEDELETED]

              (ooxml, embedded labels, table is $(vSheet);

              next;

              • Re: Reloading a cross tab field in twice

                I tried a bar chart, using Cost Centre as the Dimension, and Sum (Data) as the expression. I'm not aware of the importance of $syn tables, that sounds like it might be one of the problems...

                • Re: Reloading a cross tab field in twice

                  I was using DataCalc as my 'reload the source data again with a different name'  - there should be no difference

                  • Re: Reloading a cross tab field in twice

                    Hi, Downloading, reloading all fine. But then I deleted the chart and tried to build it again.

                     

                    The problem seems to be in the 'Edit Expression' (expression builder) box, which won't let me put that in. However, if I just write that in the expression definition box, its fine.

                     

                    Either way, it works for now - is there anything else you did to the file to make it work apart from not using the expression builder (oops... didn't think of that)?

                     

                    Thank you very much.

                      • Re: Reloading a cross tab field in twice
                        Shaun Jansen Van Nieuwenhuizen

                        No I didn't change anything, main problem is  that qlikview is case sensitive, if you are using qlikview 10 or higher, it will help you while you type to rectify case mistakes. but if you need to reload it under a dif names ect. like requested, you use resident loads.

                         

                        also, Data wont work, becasue Datacalc is placed after, so the line with the name Data in it is ignored.

                      • Re: Reloading a cross tab field in twice

                        though I wrote DataCalc on this webpage, I kept to consistent cases on the .qvw

                         

                        I think it is all a case about the expression builder stopping you doing things with key fields - I think I've had this problem before with something else - but you can overwrite that by putting straight into the file.

                         

                        I'm going to delete my file from my earlier post now. Thanks for all your help and patience.