topic Re: Cross Table in QlikView

Cross Table

Wed, 21 Aug 2013 05:00:17 GMT

Can any one explain me the difference between cross table & a straight table..........?

Re: Cross Table

khadeer — Wed, 21 Aug 2013 05:08:11 GMT

Hi,

Are you referring to Crosstable with cross tab.

Cross tables are table where facts are divided on the basis of dimension values.

for example we have sales for every month. then, if in our database we load sales amounts as a separated field for every month as

Product Jan Feb Mar etc

a 100 200 300

So, basically we have 1 single fact as sales amount here, and jan-dec are dimension memebers of month dimension but we are creating different fields for every dimension member and treating them as different field.

Loading data this way, makes it difficult to properly aggregrate the data over different dimensions and difficult to do analysis.

So, we convert this crosstable into a normal table.

using

crosstable (Month, Amount)

load

from a.qvd(qvd);

then we will get the same table as

Product Month Amount

a jan 100

a feb 200

a mar 300

Re: Cross Table

vikasmahajan — Wed, 21 Aug 2013 05:09:04 GMT

In cross table you can transform following information into row wise

DEPARTMENT	JAN	FEB	MAR	APR	MAY
A	124	546	122	375	454
B	445	464	344	232	465
C	333	122	456	454	343
D	123	353	122	477	121
E	134	657	343	575	123

For us to use this table better in a QlikView data model, we need to convert it to a

traditional table with the following structure:

DEPARTMENT	MONTH	sum(SALES)
A	APR	375
A	FEB	546
A	JAN	124
A	MAR	122
A	MAY	454
B	APR	232
B	FEB	464
B	JAN	445
B	MAR	344
B	MAY	465
C	APR	454
C	FEB	122
C	JAN	333
C	MAR	456
C	MAY	343
D	APR	477
D	FEB	353
D	JAN	123
D	MAR	122
D	MAY	121
E	APR	575
E	FEB	657
E	JAN	134
E	MAR	343
E	MAY	123

and in straight table you can select fields and display plain output.

Hope this help you to understand

Please refer cross.qvw for better understanding.

Good Luck

Vikas Mahajan

Re: Cross Table

Wed, 21 Aug 2013 05:27:21 GMT

Crosstable

A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. To turn a cross table into a straight table, use a crosstable prefix.

The syntax is:

crosstable (attribute field , data field [ , n ] ) ( loadstatement | selectstatement )

where:

attribute field is the field to contain the attribute values.

data field is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

Examples:

Crosstable (Month, Sales) Load * from ex1.csv;

Crosstable (Month,Sales,2) Load * from ex2.csv;

Crosstable (A,B) Select * from table3;

Crosstable Wizard

The crosstable wizard is dialog driven method of creating the crosstable syntax. This dialog is opened by clicking the Crosstable button in the Options page of the File Wizard. The crosstable wizard holds the following options:

Qualifier Fields	The number of qualifier fields that precede the fields to be transformed.
Attribute Field	The name of the new field that will contain all the fields (attribute values) to be transformed.
Data Field	The name of the new field that will contain the data of the attribute values.

Straight Table

An example of a Straight table

Charts are graphical representations of numerical data. The pivot table and the straight table are special cases as they display the data in table form while retaining all the properties of a chart. It is possible to switch between different representations of an existing chart, by changing the Chart Type in the Chart Properties: General page.

In opposition to the pivot table, the straight table cannot display sub-totals or serve as a cross table. On the other hand, any of its columns can be sorted and each of its rows contains one combination of dimension(s)+expression(s).

The quickest way to create a new straight table is to select Quick Chart Wizard from the Tools menu.

By a right-click on the straight table the Straight Table: Object Menu will be displayed. It can also be accessed from the Object menu, when the straight table is the active object.

Re: Cross Table

vikasmahajan — Wed, 21 Aug 2013 05:32:50 GMT

Still you want know diff ?

Re: Cross Table

Wed, 21 Aug 2013 06:18:47 GMT

see attachment of Crosstable

Re: Cross Table

Wed, 21 Aug 2013 13:58:51 GMT

Thank you vikas it is informative , help me solve my problem,

Month A B C

Jan 10 15 20

Feb 25 30 5

Mar 7 14 19

Here,

The numbers represent the customers,

Products are (A,B,C),&

Month.

Month & Products are fields & i want to transpose them how can i do that..? And i want the same count even after transposing.