crosstable

[fmazzarelli]

Hi Community,

I want to improve the methodology for data modeling and I was interested in creating a crosstab.

But I cannot resolve.

Could you give me a hand?

TABLETICKET:

LOAD.....

Anno,

PRIORITY

.....

NEWTABLE:

CrossTable (Anno, PRIORITY)

LOAD

RECNO AS NUMRECORD,

Anno,

PRIORITY,

FROM TABLETICKET;

But is it normal that I could create a "new table containing only 2 fields?

[Gysbert_Wassenaar]

I don't see a CONNECT statement in the section of the script in your screenshot. Perhaps you should move that section down below the section where you connect to the database.

[fmazzarelli]

Hi,

sequence of my tables is.

1) newtable (table using tableticket fields)

2) TABLETICKET (table using as400 fields)

table is created using sql select DATA1 DATA2 DATA3 from ...

3) odbc connection (as400) containing

LIB CONNECT TO 'myapp';

[martinpohl]

I think you want to referenz at the NewTable-statement to your loaded (or selected) Tableticket before.

For that you have to use

resident

instead of

from

Regards

[fmazzarelli]

Hi,

sorry, doesn't work

I modified my script using Resident instead of From

TABLESLA:

CrossTable(Anno, PRIORITY)

LOAD

RECNO AS NUMRECORD,

Anno,

PRIORITY

Resident TABLETICKET;

[martinpohl]

what is the error message?

[fmazzarelli]

Hi

[petter]

Your problem here is that you have Anno and PRIORITY mentioned in the CROSSTABLE() and they also appear in the LOAD which is not permitted.

The CROSSTABLE prefix for LOAD or SELECT is meant to make a wide table with repeating columns into a much taller table with no repeating columns. The classic example is a budget table:

BUDGET2017:

LOAD * INLINE [

Account, Jan, Feb, Mar, Apr, May, June ......

ABC, 10 , 11 , 12 , 10 , ......

DEF, 5 , 6 , 7 , 8 , 7 , .....

];

This can be transformed into a table that would look like this:

Account, Month, Budget

ABC, Jan, 10

ABC, Feb, 11

ABC, Mar, 12

...

DEF, May, 7

...

The CROSSTABLE LOAD would look like this:

CROSSTABLE(Month,Budget)

LOAD

     Account,Jan,Feb,Mar,Apr,May,June ....     // or simply *

RESIDENT

     BUDGET2017;

NOTICE!   Month and Budget are two NEW fields and they cannot appear in the following LOAD.

So this is often what is called an UNPIVOT operation pivoting you data clock-wise from a wide to a tall format.

The syntax is this:

CROSSTABLE( NewAttributeField, NewDataField, <number-of-existing-attribute-fields> )

The third parameter is optional and defaults to 1 if not specified. That's why the example above doesn't need it.

It is a specification of all attributes that should be kept as is and not be unpivoted.