7 Replies Latest reply: Jan 6, 2017 6:06 PM by Petter Skjolden RSS

    crosstable

    Fabio Mazzarelli

      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

        • Re: crosstable
          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.

          • Re: crosstable
            Martin Pohl

            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

            • Re: crosstable
              Petter Skjolden

              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.