Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
fmazzarelli
Not applicable

crosstable

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?

Immagine.png

7 Replies
Gysbert_Wassenaar
Not applicable

Re: crosstable

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.


talk is cheap, supply exceeds demand
fmazzarelli
Not applicable

Re: crosstable

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
Not applicable

Re: crosstable

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
Not applicable

Re: crosstable

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
Not applicable

Re: crosstable

what is the error message?

fmazzarelli
Not applicable

Re: crosstable

Hi

Immagine.png

petter
Not applicable

Re: crosstable

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.