Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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';
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
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;
what is the error message?
Hi
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.