topic Re: pls give me a explination about cross table with simple example? in QlikView

pls give me a explination about cross table with simple example?

Thu, 06 Mar 2014 16:18:27 GMT

pls give me a explination about cross table with simple example?

Re: pls give me a explination about cross table with simple example?

Anonymous — Thu, 06 Mar 2014 16:21:07 GMT

Hope this helps

CrossTable.qvw

Re: pls give me a explination about cross table with simple example?

fkeuroglian — Thu, 06 Mar 2014 16:25:36 GMT

Hi, see this example

good luck

Fernando

QlikView Examples: Using the Crosstable Feature in QlikView

Cross Table in Qlikview « Learn Qlikview

Re: pls give me a explination about cross table with simple example?

Thu, 06 Mar 2014 16:30:32 GMT

sorry i am using personal edition i cant open pls send as a file

Re: Re: pls give me a explination about cross table with simple example?

MK_QSL — Thu, 06 Mar 2014 16:30:59 GMT

Example enclosed.

UPDATE:

Consider that you have customer vs monthly sales data as below..

Customer	Country	Jan	Feb	Mar
A	Germany	100	200	300
B	Spain	400	500	250
C	Italy	350	250	600

Use below script and you will see how cross table is working..

CrossTable(Month, Sales, 2)

Load * Inline

[

Customer, Country, Jan, Feb, Mar

A, Germany, 100, 200, 300

B, Spain, 400, 500, 250

C, Italy, 350, 250, 600

];

The table will now looks like below....

Customer	Country	Month	Sales
A	Germany	Feb	200
A	Germany	Jan	100
A	Germany	Mar	300
C	Italy	Feb	250
C	Italy	Jan	350
C	Italy	Mar	600
B	Spain	Feb	500
B	Spain	Jan	400
B	Spain	Mar	250

Re: pls give me a explination about cross table with simple example?

Anonymous — Thu, 06 Mar 2014 16:37:36 GMT

The cross table is useful when we want to convert our columns into rows.

This is an example of a cross table:

Sales Person	Q1	Q2	Q3	Q4
John	50	30	20	10
Mary	30	10	50	10

During the load the data will be transformed to this format:

Sales Person	Quarter	Sales
John	Q1	50
Mary	Q1	30
John	Q2	30
Mary	Q2	10
John	Q3	20
Mary	Q3	50
John	Q4	10
Mary	Q4	10

Load Script:

CrossTable(Quarter, Sales)

LOAD [Sales Rep],

[Q1 2010],

[Q2 2010],

[Q3 2010],

[Q4 2010],

[Q1 2011],

[Q2 2011],

[Q3 2011],

[Q4 2011],

[Q1 2012],

[Q2 2012],

[Q3 2012],

[Q4 2012]

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'Total'))),

Replace(1, top, StrCnd(null))

));

Re: pls give me a explination about cross table with simple example?

Thu, 06 Mar 2014 16:54:38 GMT

crosstable (attribute field , data field [ , n ] )

here what about n what it will do exactly and how to identify which one is attribute field and which one is data field

Re: pls give me a explination about cross table with simple example?

Anonymous — Thu, 06 Mar 2014 17:04:37 GMT

Crosstable : n signifies the Qualifier fields

CrossTable(Quarter, Sales) by default its 1,its sales person in my example and

in manish example its Customer and Country,so he assigned 2 to n.

Crosstable will not evaluate qualifier fields and from next field it transforms rows to columns.

Re: pls give me a explination about cross table with simple example?

aveeeeeee7en — Thu, 06 Mar 2014 17:11:15 GMT

Hi Manoj

In Simple Language

Question: why we use Cross Table?

Answer: We All like to read data in a easier way. So, in order to convert Horizontal Columns into Vertical Rows we use Cross Table Functionality and this increases the readability of data which is easier to understand.

Regards

Aviral Nag

Re: pls give me a explination about cross table with simple example?

aveeeeeee7en — Thu, 06 Mar 2014 17:14:23 GMT

Brief:

Crosstable

A cross table is a common type of table featuring a matrix of values between two or more orthogonal lists of header data, of which one is used as column headers. A typical example could be to have one column per month. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. month names) will be stored in one field - the attribute field - and the column data (month numbers) will be stored in a second field: the data field.

The syntax is:

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

where:

attribute field name is the field to contain the attribute values.

data field name 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 statement. 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.