Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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]

14 Replies
pho3nix90
Creator II
Creator II

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;

pho3nix90
Creator II
Creator II

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

Not applicable
Author

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

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

next;

pho3nix90
Creator II
Creator II

I might not be understanding you correctly. What exactly is wrong? could you supply an example qvw?

Not applicable
Author

Thanks for your patience on this Qlikview newbie! I've tried recreating the problem with sample data for upload, but I can't get it recreate the problem. I'm happy to upload my existing file if I can then delete it from the thread once the problem is fixed - do you know if thats possible?

As I understand the problem, QV is creating a field 'Data' from my cross tab files in excel (actually five spreadsheets, each with multiple sheets, I think this might be relevant as it didn't cause problems when it wasn't this complex in the sample versions I was getting out).

QV makes the Data a KEY field as its common across all spreadsheets

I would like to make the Data the subject of an expression, but QV won't let me do this unless I load Data in again as a new field with a different name from the primary data. But as it doesn't exist in the primary data (QV has calculated it from the cross tab format) it doesn't work...

If you can't help, really no problems, we've got suppliers who should be able to help later in the week... just wanted to see if I could fix the problem myself.

pho3nix90
Creator II
Creator II

Hey, yeah it's possible, or you can PM it to me if you prefer it that way, it's fixable. and will be glad to help. but if you want, you can put in fake values.

pho3nix90
Creator II
Creator II

what type of chart is it, and what did you use as the dimension? One thing I noticde is you have $syn tables, you must try and only link with 1 field, otherwise make a link field.

Not applicable
Author

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...

pho3nix90
Creator II
Creator II

What will the dif be between the Data field, and datacalc field?