Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Convert columns of data to rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Convert columns of data to rows
 Login/Join
 
Silver Member
posted
I have 2 rows of data with 8 columns:

Sort_Fld Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8
000000000 123 4560 37 12.00% 88.00% 67.00% 17.00% 5.00%
999999999 56789123 999999976 18 10.00% 90.00% 49.00% 22.00% 8.00%

Sort_Fld - format I9
Column_1 to Column_3 - format I9
Column_4 to Column_8 - format D7.2%

I need to convert it to 16 rows of data:

Sort_Fld Verb_Fld
000000000 123
000000000 4560
000000000 37
000000000 12
000000000 88
000000000 67
000000000 17
000000000 5
999999999 56789123
999999999 999999976
999999999 18
999999999 10
999999999 90
999999999 49
999999999 22
999999999 8

My initial solution:

1. Did 8 TABLE FILE requests to get the 2 records for each column into 8 individual files:

TABLE FILE THATFILE
PRINT Column_X
BY Sort_Fld
ON TABLE HOLD AS COLUMN_X
END

2. Then I used MATCH FILE with MORE to concatenate those 16 records into 1 file.
3. After the MATCH FILE, used a DEFINEd counter to identify each row of the MATCH output.
4. Concatenated the counter to the Sort_Fld to give each row a unique value (eg. FOR_FLD).
5. Additionally, the 000000000 records needed to be displayed in one column and the 999999999 records displayed in another column, so I created 2 "bucket" fields to take care of this.
6. FOR_FLD was used as the FOR field to paint the report in the FML painter.

FINAL OUTPUT

000's 999's
Description_Line 1 123 56789123
Description_Line 2 4560 999999976
Description_Line 3 37 18
Description_Line 4 12 10
Description_Line 5 88 90
Description_Line 6 67 49
Description_Line 7 17 22
Description_Line 8 5 8


This issue:

To satisfy the rules of the MORE (universal concatenation) command, all verb objects were converted to a single format (D12) from their original I9 and D7.2% formats.

How can I get these formats changed back in the final report? Or is there another way to get the report to display as shown in the FINAL OUTPUT? The FML painter was my idea since it allowed for free form "painting" of the report - FML is not a requirement.

Marcia

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Expert
posted Hide Post
Marcia, two things.

1. I would use the Macguver technique to make the columns rows. I just posted a similar solution.

2. Are you aware that you can have a dynamic format.

If you make sure that your data files that is converted to rows from columns has an extra column that contains the format of the field, then you can use it. e.g. DATAFIELD/FORMATFIELD.

As long as your numeric column contains enough decimal and unit places to cater for all formats, this should work.


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
Thanks for your response, Waz!

MacGyver technique has never made sense to me. Which thread contains your mentioned 'similar solution' and I can take a look at it to see if I can use it for this.


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Expert
posted Hide Post
Have a look at http://forums.informationbuild...71057331/m/988102255.

And Macgyver concept is quite simple if you strip away the myth.

In this incarnation, all we are doing is creating a table of numbers that can be joined to. By joining avery record from one file to every record from the other, we can then assing a column to a row number.


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
I like that EDAPUT technique for creating the MASTER on the fly, BUT I can't find the command. I get an error when I try to run block of code as is.

Do you have any idea where this command is located in version 7.6.10 of WebFOCUS?


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Expert
posted Hide Post
What error do you get ?

Can you post the output ?


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key -LINES.fex.

Here is the code that I copied from your other case:

EX -LINES 7 EDAPUT MASTER,fseq,CV,FILE
FILE=FSEQ, SUFFIX=FIX
SEGNAME=SEG1
FIELD=CONTROL, BLANK, A1, A1, $
SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
FIELD=WHATEVER, , A1, A1, $
FIELD=COUNTER, ORDER, I4, I4,$


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Expert
posted Hide Post
ERROR_MR_FEX_NOT_FOUND means you are running the fex from MRE. As MRE scans the code for -INCLUDE and EX commands, and the EDAPUT command is internal, it will fail.

What you need to do is wrap the 7 lines of the commmand in -MRNOEDIT BEGIN and END.

-MRNOEDIT BEGIN
EX -LINES 7 EDAPUT MASTER,fseq,CV,FILE
FILE=FSEQ, SUFFIX=FIX
SEGNAME=SEG1
FIELD=CONTROL, BLANK, A1, A1, $
SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
FIELD=WHATEVER, , A1, A1, $
FIELD=COUNTER, ORDER, I4, I4,$
-MRNOEDIT 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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
Thanks, Waz!

On to the world of MacGyver...


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Expert
posted Hide Post
MacGyver away, and have fun.

Big Grin


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
For your consideration, try running this:

-* Create starting data.
SET PAGE=NOLEAD
TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP
ON TABLE HOLD AS 'car1'
END
TABLE FILE CAR1
PRINT *
END
-*
-* Anti-Cross tab of &TABLE.
-SET &TABLE = 'CAR1';
APP FI assign DISK assign.fex
TABLE FILE syscolum
PRINT COMPUTE Line/I3 = IF LAST Line EQ 0 THEN 1 ELSE Line+1; NOPRINT
COMPUTE Assign/A99 = 'IF Line EQ ' | EDIT(Line) | ' THEN ' | NAME || ' ELSE';
WHERE TBNAME EQ '&TABLE'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS Assign FORMAT ALPHA
END
-RUN
-*
TABLE FILE syscolum
PRINT NAME
WHERE TBNAME EQ '&TABLE'
ON TABLE HOLD AS syscol
END
-*
SQL SELECT * FROM syscol,&TABLE;
TABLE ON TABLE HOLD AS combined
END
-*
TABLE FILE combined
PRINT NAME
COMPUTE Line/I3 = IF LAST Line EQ 0 THEN 1 ELSE Line+1; NOPRINT
COMPUTE Measure/P20.2 =
-INCLUDE assign
0;
END
  


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Silver Member
posted Hide Post
Hmmmm, David, I like where you are going with this. As I was looking at this solution I noted the -INCLUDE assign.

Does the top procedure represent the content of the assign.fex?

What I am seeing so far with this seems clearer than MacGyver to me.

I appreciate your post and look forward to the clarification.

Thanks,

Marcia


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Platinum Member
posted Hide Post
Yes, the first procedure creates the assign.fex, it generates:

IF Line EQ 001 THEN DEALER_COST ELSE
IF Line EQ 002 THEN RETAIL_COST ELSE
IF Line EQ 003 THEN SALES ELSE
IF Line EQ 004 THEN LENGTH ELSE
IF Line EQ 005 THEN WIDTH ELSE
IF Line EQ 006 THEN HEIGHT ELSE
IF Line EQ 007 THEN WEIGHT ELSE
IF Line EQ 008 THEN WHEELBASE ELSE
IF Line EQ 009 THEN FUEL_CAP ELSE

If you comment the code line TABLE ON TABLE HOLD AS COMBINED from the SQL query, you will see what it creates and understand how the assign.fex logic picks off a different column value for each row to assign to the Measure column.
The SQL query is matching the SYSCOLUM data, one row for each original column, to the original data row using a Cartesian product. So this same approach can work even if your original source data has more than one row, but that would require a few more tweaks.
I hope this helps.


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Expert
posted Hide Post
I think it would be realy interesting to see some extra code to handle other formats, as well.

If everything was converted to Strings, then it would not matter what the source data was.

You could also create a companion format field for display purposes.

Good One


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
That is what I was thinking. I did run the first block of code and it gave me the results posted and I got so excited that it was leading me in the right direction, but I just wanted to ensure that I was clear on what was being done.

I need to hand the final result over to my power user for maintenance going forward and I think I can talk him through this much easier than MacGyver. ;-)


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Silver Member
posted Hide Post
Everything works will until the SQL SELECT statement. Here is the output so far:

TABLE FILE syscolum
PRINT NAME
WHERE TBNAME EQ 'CAR1'
ON TABLE HOLD AS syscol
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 9 LINES= 9
-*
SQL SELECT * FROM syscol, CAR1;
TABLE
ON TABLE HOLD AS combined
END
-RUN
1
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
....

What am I doing wrong?

Marcia


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Platinum Member
posted Hide Post
Is there data in CAR1?


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Silver Member
posted Hide Post
Interestingly, there is data in car1 when the assign -INCLUDE is executed on its own, but for some reason it has 0 records when utilized in the parent/calling procedure. I am still looking into this and if you can think of anything that would cause that let me know.

Thanks, David


WebFOCUS 7.73
Windows, Unix, AS/400 (iSeries)
HTML, PDF, MS Excel (including templates), HTML Active Reports
 
Posts: 32 | Registered: October 01, 2010Report This Post
Platinum Member
posted Hide Post
If you

TABLE FILE CAR PRINT *
END

Just before the SQL query, what output do you get?


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Guru
posted Hide Post
Very good coding.
I will try to implement it here.

Congrats dbeagan


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Convert columns of data to rows

Copyright © 1996-2020 Information Builders