Cross Table

[alec1982]

Hi Guys,

I have an excel file that needs to have 2 cross tables

In the file I have

Address,

City,..

....

..

Attorny2012

Attorny 2013

Attoorny 2014

Attorny 2015

Then I have

Attorny Seat 2012

Attorny Seat 2013

Attorny Seat 2014

Attorny Seat 2015

then I have some more columns..

I tried to build a cross table to show a Year field and then Attorny field and Attorny seat field. but no success.

Anybody can help me please.

Attached is the excel file.

Thanks,

[manojkvrajan · Accepted solution]

Alec, Please use two cross tables - Attorney and AttorneySeat to transform your data source fields. Then join the two tables into one and extract the relevant fields from the excel. Attached is the sample. I hope it helps.

[alec1982]

Hi,

Thank you for the reply but this has caused an increase in all the values

like I have one of the charts is looking for Expiration by year and the field expiration has the value of the year.

in the excel file it is 1 for year 2012, and now by using the script that you sent me it is showing as 4.

Thanks,

Alec

[bgerchikov]

Alec,

Can you upload spreadsheet displaying what you want to see? I did the same way as Manoj following your explanation...

[alec1982]

Hi,

I followed the script and deleted the join and everything works fine.

Thanks,

[alec1982]

One more question:

if i have on the script the following fields in one of the table:

Address   Offices     WorkStations

ABC              2               3

XYZ               5               2

How can I create a field t be named Space type and show as follow:

Address     Space type     Count

ABC          Office               2

ABC          WorkStations   3

XYZ          Offices              5

XYZ          WorkStations    2

Thankss for your help.

[manojkvrajan]

Alec, Excel and sample qvw will be helpful to address your requirements.

Thanks,

Manoj Kumar Varatharajan

http://innoppl.com

[alec1982]

Hi,

thanks for the reply,

In the sample above in the QVW you can find the Offices and Worktations and Address field.

Thanks,

[manojkvrajan]

Alec, Please create another table OfficeWorkStation similar to Attorney and Attorneyseat. I hope this works. BTW, Join is used to have those fields in one table. However you can make a decision based on your data size.

OfficeWorkstation:

CrossTable([Space Type], Count, 1)

LOAD Address,

     Offices,

     Workstations      

FROM

[law-firm-data-1.xls]

(biff, embedded labels, table is Sheet1$);