Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Rearranging the data
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Rearranging the data
 Login/Join
 
Platinum Member
posted
Hi all;
What's the BEST way to re-arrange rows (horizontal) to a Columns (Vertical)

Example:
ACCOUNT Sep-13 Oct-13 Nov-13
Income 999.99 999.99 999.99
Exp.. .... .... .....


TO
Date Account Amount
Sep-13 Income 999.99
Oct-13 Income 999.99
Nov-13 .. ...
Sep-13 Exp. 999.99
.. .. ..

Thanks

This message has been edited. Last edited by: <Kathryn Henning>,


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
Have a look for the Macgyver technique.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6278 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks Waz..
But the Macgyver technique is repeating the rows..


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
FOCdev,
Could you clarify your data?
When you write:
  
ACCOUNT Sep-13 Oct-13 ...
Income  999.99 999.99 ...

are ACCOUNT and Sep-13 etc. fieldnames?
And Income, 999.99 values?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I figured it out..
Danny. I have data fields like Month1, Month2, Month3... and so on with other financial data in monthly buckets.. this was calculated data..
To use the same data as is, for reporting would be cumbersome, like using the headings to format the data..
For reporting purpose, so that its easier to format the report, i wanted to have this data in a Table format, so MONTH field will have 01 to 12 months values and so on..
Using Mac Tec, i repeated the rows 12 times and then using Define and COUNTER I created the MONTH field to dump each value.. following is the code.

Thanks WAZ, Danny..
others, if there is any other way, let me know..

Code
-* Use Mcgyver Technique to re-arrange the report data from rows to columns
-*Macgyver technique.
DEFINE FILE ITREND2
BLANK/A1 WITH RPTKEY = ' ';
COUNT/I1 WITH RPTKEY = 1;
END
-RUN
TABLE FILE ITREND2
PRINT
COMPUTE COUNTER/I2 = COUNT + COUNTER;
BY BLANK
ON TABLE HOLD AS FSEQ FORMAT FOCUS INDEX BLANK
-* report is for 12 months
IF RECORDLIMIT EQ 12
END
-RUN
JOIN BLANK WITH RPTKEY IN ITREND2 TO ALL BLANK IN FSEQ
-RUN
DEFINE FILE ITREND2
BLANK /A1 WITH RPTKEY = ' ';
END
-RUN
TABLE FILE ITREND2
PRINT COUNTER RPTKEY
GLRE_ACT_YR1
GLRE_ACT_YR2
GLRE_ACT_YR3
GLRE_ACT_YR4
GLRE_ACT_YR5
GLRE_ACT_YR6
GLRE_ACT_YR7
GLRE_ACT_YR8
GLRE_ACT_YR9
GLRE_ACT_YR10
GLRE_ACT_YR11
GLRE_ACT_YR12

GLRE_ACCT_MTH1
GLRE_ACCT_MTH2
GLRE_ACCT_MTH3
GLRE_ACCT_MTH4
GLRE_ACCT_MTH5
GLRE_ACCT_MTH6
GLRE_ACCT_MTH7
GLRE_ACCT_MTH8
GLRE_ACCT_MTH9
GLRE_ACCT_MTH10
GLRE_ACCT_MTH11
GLRE_ACCT_MTH12
BY RPTKEY
ON TABLE HOLD AS ITREND3 FORMAT FOCUS INDEX RPTKEY
END
-RUN
DEFINE FILE ITREND3
MONTH/A2=IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 1 THEN GLRE_ACCT_MTH1 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 2 THEN GLRE_ACCT_MTH2 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 3 THEN GLRE_ACCT_MTH3 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 4 THEN GLRE_ACCT_MTH4 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 5 THEN GLRE_ACCT_MTH5 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 6 THEN GLRE_ACCT_MTH6 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 7 THEN GLRE_ACCT_MTH7 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 8 THEN GLRE_ACCT_MTH8 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 9 THEN GLRE_ACCT_MTH9 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 10 THEN GLRE_ACCT_MTH10 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 11 THEN GLRE_ACCT_MTH11 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 12 THEN GLRE_ACCT_MTH12 ELSE ' ';
END
-RUN
TABLE FILE ITREND3
PRINT COUNTER MONTH
GLRE_ACCT_MTH1
...
...


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
FOCdev,
Could you post your .mas file?
I think I might have something simpler with a solution based on an alternate master.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Data in (rows) is in a Hold file..
ALPHANUMERIC RECORD NAMED SAVE
0 FIELDNAME ALIAS FORMAT LENGTH
RPTKEY A19 19
GLRE_ACCT_NO E01 A8 8
GLRE_LOC1 E02 A6 6
GLRE_LOC2 E03 A6 6
..
GLRE_LOC12 E13 A6 6
GLRE_ACT_YR1 E14 A4 4
GLRE_ACT_YR2 E15 A4 4
..
GLRE_ACT_YR12 E25 A4 4
GLRE_ACCT_MTH1 E26 A2 2
GLRE_ACCT_MTH2 E27 A2 2
..
GLRE_ACCT_MTH12 E37 A2 2
MTD_AMT1 E38 D12 12
MTD_AMT2 E39 D12 12
..
MTD_AMT3 E40 D12 12
MTD_AMT12 E41 D12 12
..


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
If your output is to be rows, you can use the OVER command

e.g.
TABLE FILE CAR
PRINT COUNTRY
OVER CAR
OVER MODEL
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6278 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
OVER is for display only.. I used the Mac Tech and its working.. Thanks all


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This is your master:
  
 RPTKEY A19 19
 GLRE_ACCT_NO E01 A8 8
 GLRE_LOC1 E02 A6 6
 GLRE_LOC2 E03 A6 6
 .. 
 GLRE_LOC12 E13 A6 6
 GLRE_ACT_YR1 E14 A4 4
 GLRE_ACT_YR2 E15 A4 4
 ..
 GLRE_ACT_YR12 E25 A4 4
 GLRE_ACCT_MTH1 E26 A2 2
 GLRE_ACCT_MTH2 E27 A2 2
 ..
 GLRE_ACCT_MTH12 E37 A2 2
 MTD_AMT1 E38 D12 12
 MTD_AMT2 E39 D12 12
..
 MTD_AMT3 E40 D12 12
 MTD_AMT12 E41 D12 12
..

Now if you arrange it thus:
  
 RPTKEY A19 19
 GLRE_ACCT_NO E01 A8 8
 GLRE_LOC1 E02 A6 6
 GLRE_ACT_YR1 E14 A4 4
 GLRE_ACCT_MTH1 E26 A2 2
 MTD_AMT1 E38 D12 12
 GLRE_LOC2 E02 A6 6
 GLRE_ACT_YR2 E14 A4 4
 GLRE_ACCT_MTH2 E26 A2 2
 MTD_AMT2 E38 D12 12
...
 GLRE_LOC12 E02 A6 6
 GLRE_ACT_YR12 E14 A4 4
 GLRE_ACCT_MTH12 E26 A2 2
 MTD_AMT12 E38 D12 12

you can create an alternate master:
  
FILENAME=SAVE, SUFFIX=FIX
SEGNAME=TOP, SEGTYPE=S0
 FIELDNAME=RPTKEY, USAGE=A19, ACTUAL=A19,$
 FIELDNAME=GLRE_ACCT_NO, USAGE=A8, ACTUAL=A8,$
SEGNAME=MONTHS, PARENT=TOP, OCCURS=VARIABLE
 FIELDNAME=GLRE_LOC, USAGE=A6, ACTUAL=A6,$
 FIELDNAME=GLRE_ACT_YR, USAGE=A4, ACTUAL=A4,$
 FIELDNAME=GLRE_ACCT_MTH, USAGE=A2, ACTUAL=A2,$
 FIELDNAME=MTD_AMT, USAGE=D12, ACTUAL=A12,$
 FIELDNAME=MONTH, ALIAS=ORDER, USAGE=I2, ACTUAL=I4,$

In this way you have transformed a horizontal list of fields into a repeating group of fields that can be arranged vertically. The last field is provided by WebFOCUS and gives you the order of the repeating group of fields.

This technique is called "Alternate Master".


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
Daniel - Clone of Macgyver Wink


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Daniel Thanks.. I have seen your posts on Alternate Master, technique I will try it.

Thanks


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Rearranging the data

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.