How to Cross-tab the data in Progress 4GL

Mark123 · Jan 28, 2008

Hi, I need to perform a cross-tab of data in Progress 4GL.
I have a table like this, it's called sales:

seq city date sold
----------------------------
1 New York 02/01/07 123
1 New York 03/01/07 125
1 New York 04/01/07 128
1 New York 05/01/07 132
1 New York 06/01/07 140
1 New York 07/01/07 153
1 New York 08/01/07 160
1 New York 09/01/07 160
1 New York 10/01/07 175
1 New York 11/01/07 173
1 New York 12/01/07 181
1 New York 01/01/08 188
2 Chicago 02/01/07 43
2 Chicago 03/01/07 46
2 Chicago 04/01/07 47
2 Chicago 05/01/07 51
2 Chicago 06/01/07 53
2 Chicago 07/01/07 55
2 Chicago 08/01/07 57
2 Chicago 09/01/07 56
2 Chicago 10/01/07 63
2 Chicago 11/01/07 68
2 Chicago 12/01/07 73
2 Chicago 01/01/08 75

What I need is to spread the data horizontally, like shown below:

seq city 02/01/07 03/01/07 04/01/07 05/01/07 .....
---------------------------------------------------------
1 New York 123 125 128 132 .....
2 Chicago 43 46 47 51 .....

Any help is much appreciated.

Cecil · Jan 28, 2008

Hi there.

It might look a bit nutty and complex, but I think it might put you in the right direction.

There are possible other alternatives that came to mind but this seamed more fun.

Also I don't know if it actually works, but it compiles that a start. :awink:.

Good Luck.

Code:

/* tt needed to be able to compile as I do 
   not have a sales table on my database. */
DEFINE TEMP-TABLE ttsales NO-UNDO
  FIELD seq       AS INTEGER    
  FIELD city      AS CHARACTER  
  FIELD DATE     AS DATE       
  FIELD sold      AS INTEGER
  INDEX idxSales IS PRIMARY
    seq.

DEFINE BUFFER sales FOR TEMP-TABLE ttsales.
  
DEFINE VARIABLE bh             AS HANDLE      NO-UNDO.
DEFINE VARIABLE cDateFieldName AS CHARACTER   NO-UNDO.
DEFINE VARIABLE fh             AS HANDLE      NO-UNDO.
DEFINE VARIABLE iFieldElement  AS INTEGER     NO-UNDO.
DEFINE VARIABLE qh             AS HANDLE      NO-UNDO.
DEFINE VARIABLE tth            AS HANDLE      NO-UNDO.

/* Build a DYNAMIC temp table */

CREATE TEMP-TABLE tth.

tth:ADD-NEW-FIELD('seq', 'INTEGER', 0, '', '', 'Seq',?).
tth:ADD-NEW-FIELD('city', 'CHARACTER', 0, '', '', 'City',?).

/* Build a new field for each date  */

FOR EACH sales NO-LOCK
  BREAK BY sales.DATE:

  IF FIRST-OF(sales.DATE) THEN
  DO:
    
    ASSIGN
      cDateFieldName = STRING(sales.DATE)
      cDateFieldName = REPLACE(cDateFieldName, '/','_').

  END.
    tth:ADD-NEW-FIELD(cDateFieldName, 'DATE', 0, '', '', STRING(sales.DATE),? ).

END.

/* Set the index for the dynamic temp-table. */ 
tth:ADD-NEW-INDEX('idxSaleSeq', FALSE, TRUE ) .
tth:ADD-INDEX-FIELD('idxSaleSeq','seq').

tth:TEMP-TABLE-PREPARE('tabSales').

bh = tth:DEFAULT-BUFFER-HANDLE.

/* Populate the DYNAMIC temp table */
FOR EACH sales NO-LOCK
  BREAK BY sales.seq:

  IF FIRST-OF(sales.seq) THEN
  DO:
    bh:BUFFER-CREATE().

     fh = bh:BUFFER-FIELD('seq').
     fh:BUFFER-VALUE = sales.seq.
      
     fh = bh:BUFFER-FIELD('city').
     fh:BUFFER-VALUE = sales.seq.
  END.
  
  ASSIGN
    cDateFieldName = STRING(sales.DATE)
    cDateFieldName = REPLACE(cDateFieldName, '/','_').

  fh = bh:BUFFER-FIELD(cDateFieldName).

  IF VALID-HANDLE(fh) THEN 
    fh:BUFFER-VALUE = sales.sold.

END.


CREATE QUERY qh.

qh:SET-BUFFERS(bh).

qh:QUERY-PREPARE('FOR EACH tabSales BY tabSales.seq:').

qh:QUERY-OPEN( ). 
/** THIS bit needs a bit more work **/
QUERY-BLOCK:
REPEAT:
  
  qh:GET-NEXT().

  IF qh:QUERY-OFF-END THEN
    LEAVE QUERY-BLOCK.

  DO iFieldElement = 1 TO bh:NUM-FIELDS:
    
    ASSIGN
      fh = bh:BUFFER-FIELD(cDateFieldName).
    
    /*  Dislpay the data ....*/

    DISPLAY 
      fh:BUFFER-VALUE 
      WITH FRAME a COLUMN 40 .

  END.
END.

qh:QUERY-CLOSE(). 


/* This is a bit buggy..*/
DELETE OBJECT qh.
DELETE OBJECT bh.
DELETE OBJECT tth.

Cecil · Jan 28, 2008

Hi there.

It might look a bit nutty and complex, but I think it might put you in the right direction.

There are possible other alternatives that came to mind but this seamed more fun.

Also I don't know if it actually works, but it compiles that a start. :awink:.

Good Luck.

Code:

/* tt needed to be able to compile as I do 
   not have a sales table on my database. */
DEFINE TEMP-TABLE ttsales NO-UNDO
  FIELD seq       AS INTEGER    
  FIELD city      AS CHARACTER  
  FIELD DATE     AS DATE       
  FIELD sold      AS INTEGER
  INDEX idxSales IS PRIMARY
    seq.

DEFINE BUFFER sales FOR TEMP-TABLE ttsales.
  
DEFINE VARIABLE bh             AS HANDLE      NO-UNDO.
DEFINE VARIABLE cDateFieldName AS CHARACTER   NO-UNDO.
DEFINE VARIABLE fh             AS HANDLE      NO-UNDO.
DEFINE VARIABLE iFieldElement  AS INTEGER     NO-UNDO.
DEFINE VARIABLE qh             AS HANDLE      NO-UNDO.
DEFINE VARIABLE tth            AS HANDLE      NO-UNDO.

/* Build a DYNAMIC temp table */

CREATE TEMP-TABLE tth.

tth:ADD-NEW-FIELD('seq', 'INTEGER', 0, '', '', 'Seq',?).
tth:ADD-NEW-FIELD('city', 'CHARACTER', 0, '', '', 'City',?).

/* Build a new field for each date  */

FOR EACH sales NO-LOCK
  BREAK BY sales.DATE:

  IF FIRST-OF(sales.DATE) THEN
  DO:
    
    ASSIGN
      cDateFieldName = STRING(sales.DATE)
      cDateFieldName = REPLACE(cDateFieldName, '/','_').

  END.
    tth:ADD-NEW-FIELD(cDateFieldName, 'DATE', 0, '', '', STRING(sales.DATE),? ).

END.

/* Set the index for the dynamic temp-table. */ 
tth:ADD-NEW-INDEX('idxSaleSeq', FALSE, TRUE ) .
tth:ADD-INDEX-FIELD('idxSaleSeq','seq').

tth:TEMP-TABLE-PREPARE('tabSales').

bh = tth:DEFAULT-BUFFER-HANDLE.

/* Populate the DYNAMIC temp table */
FOR EACH sales NO-LOCK
  BREAK BY sales.seq:

  IF FIRST-OF(sales.seq) THEN
  DO:
    bh:BUFFER-CREATE().

     fh = bh:BUFFER-FIELD('seq').
     fh:BUFFER-VALUE = sales.seq.
      
     fh = bh:BUFFER-FIELD('city').
     fh:BUFFER-VALUE = sales.city.
  END.
  
  ASSIGN
    cDateFieldName = STRING(sales.DATE)
    cDateFieldName = REPLACE(cDateFieldName, '/','_').

  fh = bh:BUFFER-FIELD(cDateFieldName).

  IF VALID-HANDLE(fh) THEN 
    fh:BUFFER-VALUE = sales.sold.

END.


CREATE QUERY qh.

qh:SET-BUFFERS(bh).

qh:QUERY-PREPARE('FOR EACH tabSales BY tabSales.seq:').

qh:QUERY-OPEN( ). 
/** THIS bit needs a bit more work **/
QUERY-BLOCK:
REPEAT:
  
  qh:GET-NEXT().

  IF qh:QUERY-OFF-END THEN
    LEAVE QUERY-BLOCK.

  DO iFieldElement = 1 TO bh:NUM-FIELDS:
    
    ASSIGN
      fh = bh:BUFFER-FIELD(cDateFieldName).
    
    /*  Dislpay the data ....*/

    DISPLAY 
      fh:BUFFER-VALUE 
      WITH FRAME a COLUMN 40 .

  END.
END.

qh:QUERY-CLOSE(). 


/* This is a bit buggy..*/
DELETE OBJECT qh.
DELETE OBJECT bh.
DELETE OBJECT tth.

Mark123 · Jan 29, 2008

Wow ! It's so huge, I did not expect so. Thanks a lot Cecil, I copied it and will try to implement.

Osborne · Jul 24, 2013

Ignore, posted in wrong thread.

How to Cross-tab the data in Progress 4GL

Mark123

New Member

Cecil

19+ years progress programming and still learning.

Cecil

19+ years progress programming and still learning.

Mark123

New Member

Osborne

Active Member

Similar threads