topic Re: Cross Table in QlikView

Cross Table

alec1982 — Thu, 23 Aug 2012 15:49:24 GMT

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,

Re: Cross Table

manojkvrajan — Thu, 23 Aug 2012 16:20:55 GMT

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.

Re: Cross Table

alec1982 — Thu, 23 Aug 2012 18:31:28 GMT

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

Re: Cross Table

bgerchikov — Thu, 23 Aug 2012 20:22:01 GMT

Alec,

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

Re: Cross Table

alec1982 — Thu, 23 Aug 2012 20:39:03 GMT

Hi,

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

Thanks,

Re: Cross Table

alec1982 — Thu, 23 Aug 2012 20:42:48 GMT

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.

Re: Cross Table

manojkvrajan — Thu, 23 Aug 2012 20:49:00 GMT

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

Thanks,

Manoj Kumar Varatharajan

http://innoppl.com

Re: Cross Table

alec1982 — Thu, 23 Aug 2012 21:02:29 GMT

Hi,

thanks for the reply,

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

Thanks,

Re: Cross Table

manojkvrajan — Thu, 23 Aug 2012 21:38:19 GMT

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$);