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,

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.

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.

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.

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?

What error do you get ?

Can you post the output ?

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,$

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

Thanks, Waz!

On to the world of MacGyver...

MacGyver away, and have fun.

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

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

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.

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.

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. ;-)

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

Is there data in CAR1?

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

If you

TABLE FILE CAR PRINT *
END

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

Very good coding.
I will try to implement it here.

Congrats dbeagan