topic Re: Reloading a cross tab field in twice in QlikView

Reloading a cross tab field in twice

Tue, 06 Sep 2011 08:44:08 GMT

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

pho3nix90 — Tue, 06 Sep 2011 08:49:04 GMT

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

pho3nix90 — Tue, 06 Sep 2011 08:50:35 GMT

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

Tue, 06 Sep 2011 08:57:54 GMT

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;

Re: Reloading a cross tab field in twice

pho3nix90 — Tue, 06 Sep 2011 09:00:11 GMT

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

Re: Reloading a cross tab field in twice

Tue, 06 Sep 2011 10:00:47 GMT

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.

Re: Reloading a cross tab field in twice

pho3nix90 — Tue, 06 Sep 2011 10:04:41 GMT

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.

Re: Reloading a cross tab field in twice

pho3nix90 — Tue, 06 Sep 2011 10:25:34 GMT

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.

Re: Reloading a cross tab field in twice

Tue, 06 Sep 2011 10:27:59 GMT

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

pho3nix90 — Tue, 06 Sep 2011 10:30:49 GMT

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

Re: Reloading a cross tab field in twice

Tue, 06 Sep 2011 10:32:52 GMT

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

pho3nix90 — Tue, 06 Sep 2011 10:36:57 GMT

Check if the file is fixed now, do you get errors when you reload? remove the "CrossTable(Funding, Data, 7)" line.

Also remember, qlikview is VERY case sensitive, so datacalc wont work if it's Datacalc.

Re: Reloading a cross tab field in twice

Tue, 06 Sep 2011 10:44:32 GMT

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

pho3nix90 — Tue, 06 Sep 2011 10:51:59 GMT

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

Tue, 06 Sep 2011 10:54:35 GMT

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.