Combining Crosstables

[#1]

Hi all,

I currently have 10 cross tables loaded into my document that follow the same naming convention (i.e S1Crosstable, S1CrosstableData / S2Crosstable, S2CrosstableData) and have the same column names within each of them which each contain the same type of data (They are a unique identifier, Ind and Org).

Is there a way in which to combind these all into the same table? Currently my expression looks like this;

((count({<S1IndOrgData= {'Green', 'Blue'}>}S1IndOrgData)

  + count({<S2IndOrgData= {'Green', 'Blue'}>}S2IndOrgData)

  + count({<S3IndOrgData= {'Green', 'Blue'}>}S3IndOrgData)

  + count({<S4IndOrgData= {'Green', 'Blue'}>}S4IndOrgData)

  + count({<S5IndOrgData= {'Green', 'Blue'}>}S5IndOrgData)

  + count({<S6aIndOrgData= {'Green', 'Blue'}>}S6aIndOrgData)

  + count({<S6bIndOrgData= {'Green', 'Blue'}>}S6bIndOrgData)

  + count({<S7IndOrgData= {'Green', 'Blue'}>}S7IndOrgData)

  + count({<S8IndOrgData= {'Green', 'Blue'}>}S8IndOrgData)

  + count({<S9IndOrgData= {'Green', 'Blue'}>}S9IndOrgData))

/

(count(S1IndOrgData)

  + count(S2IndOrgData)

  + count(S3IndOrgData)

  + count(S4IndOrgData)

  + count(S5IndOrgData)

  + count(S6aIndOrgData)

  + count(S6bIndOrgData)

  + count(S7IndOrgData)

  + count(S8IndOrgData)

  + count(S9IndOrgData)))

My dimension then currently looks like this;

=if(S1IndOrg or S2IndOrg or S3IndOrg or S4IndOrg or S5IndOrg or S6aIndOrg or S6bIndOrg or S7IndOrg or S8IndOrg or S9IndOrg ='Ind', 'Ind',

if(S1IndOrg or S2IndOrg or S3IndOrg or S4IndOrg or S5IndOrg or S6aIndOrg or S6bIndOrg or S7IndOrg or S8IndOrg or S9IndOrg ='Org', 'Org', Null()))

I am getting data brought in, but it's percentages are out. When I do each of the expressions in a text box I'm getting the correct raw numbers, so it appears it's my dimension not splitting out right that is causing the issue.

Anyone have any ideas how to combine the labels for my dimension so I can show it within the chart? I'd prefer not to have to add anything to the script but if it really needs to be done then so be it.

Thanks!

[juraj_misina · Accepted solution]

Hi David,

you can concatenate the tables and rename fields to common field labels, thus you will be able to simplify your expressions. First, you need to create temporary tables when doing the crosstable load (crosstable load is a bit picky, so be careful). You can try:

Tmp_Table1:

CrossTable()

LOAD

S1IndOrg

S1IndOrgData

from [SourceFile1];

Tmp_Table2:

CrossTable()

LOAD

S2IndOrg

S2IndOrgData

from [SourceFile2];

(...)

Final_Table:

LOAD

S1IndOrg as IndOrg,

S1IndOrgData as IndOrgData

Resident Tmp_Table1;

Drop Table Tmp_Table1;

Concatenate(Final_Table)

LOAD

S2IndOrg as IndOrg,

S2IndOrgData as IndOrgData

Resident Tmp_Table2;

Drop Table Tmp_Table2;

(...)

[stigchel]

If I've read your post correctly, the source data all have the same column names, unique identifier, Ind and Org? So why rename them, you can't use the concatenate statement in combination with a CrossTable load, but qv will auto concatenate crosstables with exactly the same Field names. So alternatively to the above, keep the field names in the crosstable load the same. This example will create only one cr1 table with all rows from cr1 and cr2:

Cr1:

CrossTable(Atttribute,Value,1) LOAD *;

Load * Inline [Id,Color,Size

1,Blue,Small

2,Red,Big

];

Cr2:

CrossTable(Atttribute,Value,1) LOAD *;

Load * Inline [Id,Color,Size

3,Blue,Small

4,Red,Big

];

[#4]

Hi to both!

Sorry for taking a few days to reply but both answers worked for me. I've went with the first reply as it as I was stuck for time but I've played about with both and got both working now and will keep them in mind for the future.

Thanks!