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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to concatinate values from rows into one

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to concatinate values from rows into one
 Login/Join
 
<new2focus>
posted
I have a table store_master with 3 columns - region char(4), state char(2), store_number number(5)
the data looks like this
WEST, CA, 1
WEST, CA, 2
EAST, NY, 30
EAST, NY, 31
EAST, NY, 32
WEST, CA, 3
WEST, CA, 4
WEST, CA, 5
EAST, PA, 50
EAST, PA, 51
SOUTH, GA, 100
SOUTH, GA, 101
WEST, NV, 10
WEST, NV, 11
WEST, NV, 12
EAST, NY, 33

The rpeort should look like this.. (Region - state - all store numbers for the state seperated by '-' and presented inside a grid)
-------------------------
|EAST  | NY |30-31-32-33|
-------------------------
|EAST  | PA |50-51       |
-------------------------
|SOUTH| GA |100-101    |
-------------------------
|WEST  | CA |1-2-3-4-5 |
-------------------------
|WEST  | NV |10-11-12  |
--------------------------

I am new to webfocus and have no idea how to create this report. Could anyone please help me in creating this report?
 
Report This Post
Guru
posted Hide Post
Something like this:

DEFINE FILE STORE_MASTER
CONCAT_STORES/A100=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN CONCAT_STORES || '-' || STORE_NUMBER ELSE STORE_NUMBER;
END

TABLE FILE STORE_MASTER
SUM CONCAT_STORES
BY REGION
BY STATE
END

You will have to mess with the "A30" format of CONCAT_STORES to match the maximum number of STORE_NUMBERS that will be on one line together with room for the hyphens.
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Platinum Member
posted Hide Post
Note that N. Selph's code works well, but it assumes a couple of things. Most importantly, your data has to be in Region, State, Store order. Doesn't look like yours is. If it is not, you can get it that way by adding a little code before his (hers?), like so:

TABLE FILE STORE_MASTER
BY REGION
BY STATE
BY STORE_NUMBER
ON TABLE HOLD
END

DEFINE FILE HOLD
CONCAT_STORES/A100=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN CONCAT_STORES || '-' || STORE_NUMBER ELSE STORE_NUMBER;
END

TABLE FILE HOLD
SUM CONCAT_STORES
BY REGION
BY STATE
END

This code also assumes that store number is alpha, rather than numeric, as the concatenation feature (|) does not work on numeric fields. If store number is integer, you could change the define like so:

DEFINE FILE HOLD
CONCAT_STORES/A100=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN CONCAT_STORES || '-' || EDIT(STORE_NUMBER) ELSE EDIT(STORE_NUMBER);
END

Note also that strong concatenation (||) will not give you spaces on wither side of the dashes. You'll have to use weak concatenation (single |) for that. Then you'll end up with multiple spaces after the dash, in some cases. If that gives you trouble, you could email me, as this post is starting to get rather long.
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
<new2focus>
posted
I tried with code and it is giving the following error.
TABLE FILE WM_STORE_MASTER
BY REGION
BY STATE
BY STORE_NUMBER
ON TABLE HOLD
END
DEFINE FILE HOLD
CONCAT_STORES/A450=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN
CONCAT_STORES || '-' || EDIT(STORE_NUMBER) ELSE EDIT(STORE_NUMBER);
END
TABLE FILE HOLD
SUM CONCAT_STORES
BY REGION
BY STATE
END
0 NUMBER OF RECORDS IN TABLE= 158 LINES= 158
0 ERROR AT OR NEAR LINE 26 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
CONCAT_STORES
0 ERROR AT OR NEAR LINE 34 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CONCAT_STORES
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Could any one help me fix this error?
 
Report This Post
Member
posted Hide Post
Try with field format less then A256.
 
Posts: 20 | Location: Michigan | Registered: November 23, 2004Report This Post
<Pietro De Santis>
posted
Your DEFINE for CONCAT_STORES does not work because you're appending other fields to CONCAT_STORES. It is defined as 450 characters, but you're appending STORE_NUMBER (which will be 4 characters long, for example) to CONACT_STORES which is 450 characters - 450 + 4 is longer that 450, hence the error.

The best way to provide a solution is to give you a working example.

I will user the EDUCFILE file, provided with each installation of FOCUS.

-SET &ECHO=ALL;
TABLE FILE EDUCFILE
PRINT COURSE_CODE
BY EMP_ID
ON TABLE HOLD AS HOLD01
END
DEFINE FILE HOLD01
CONCAT_COURSE/A200 = IF EMP_ID EQ LAST EMP_ID
THEN (SUBSTR(200, CONCAT_COURSE, 1, ARGLEN(200, CONCAT_COURSE, 'I4'), ARGLEN(200, CONCAT_COURSE, 'I4'), 'A190')
|| '-' || COURSE_CODE) ELSE COURSE_CODE;
END
TABLE FILE HOLD01
SUM CONCAT_COURSE
BY EMP_ID
END
-* Syntax for Substring function: SUBSTR(inlength, parent, start, end, sublength, outfield)

Because you want to concatenate more data to an existing field, you have to use the SUBSTR function to take a piece of the original field and append the extra field.

You have to make sure that the concatenated field is large enough (A200 in my example) to include all the store numbers and a little more - to accomodate the SUBSTR function.
 
Report This Post
<new2focus>
posted
I followed Pietro's example and it worked like a charm. I truly appreciate all you people for your time and effort in helping beginners like me.

Is there any way I can strip out the leading zeroes from my output string, which is given below.
002-009-014-015-022-024-030-032-033-035-037-
040-048-051-052-064-097-118-137-140-177-198-203-205
DEFINE FILE HOLD01
CONCAT_STORES/A600 = IF REGION EQ LAST REGION AND STATE EQ LAST STATE
THEN (SUBSTR(600, CONCAT_STORES, 1, ARGLEN(600, CONCAT_STORES, 'I3'), ARGLEN(600, CONCAT_STORES, 'I3'), 'A590')
ELSE EDIT(STORE_NUMBER, '$$$999');
END

This message has been edited. Last edited by: <Mabel>,
 
Report This Post
<Pietro De Santis>
posted
What is the format of STORE_NUMBER in the Master?
 
Report This Post
<new2focus>
posted
FIELD=STORE_NUMBER,STORE_NUMBER, P6 ,P8 ,MISSING=ON ,$

NUMBER(5) IN ORACLE
 
Report This Post
<Pietro De Santis>
posted
Then, I'm surprised that EDIT(STORE_NUMBER, '$$$999') would work, since EDIT will only work with integer and alpha.

Meanwhile, here is my example updated:

-SET &ECHO=ALL;
TABLE FILE EDUCFILE
PRINT COURSE_CODE
BY EMP_ID
ON TABLE HOLD AS HOLD01
END
DEFINE FILE HOLD01
YCOURSE_CODE/I8 = EDIT(COURSE_CODE);
XCOURSE_CODE/D8c = YCOURSE_CODE;
WCOURSE_CODE/A8 = FTOA(XCOURSE_CODE, '(D8c)', 'A8');
ZCOURSE_CODE/A8 = LJUST(8, WCOURSE_CODE, 'A8');
CONCAT_COURSE/A200 = IF EMP_ID EQ LAST EMP_ID
THEN (SUBSTR(200, CONCAT_COURSE, 1, ARGLEN(200, CONCAT_COURSE, 'I4'), ARGLEN(200, CONCAT_COURSE, 'I4'), 'A190')
|| '-' || ZCOURSE_CODE) ELSE ZCOURSE_CODE;
END
TABLE FILE HOLD01
SUM CONCAT_COURSE
BY EMP_ID
END


The definition of YCOURSE_CODE is only there so that I can have a P8 field as per your field STORE_NUMBER. Basically, I use the FTOA function to convert the numeric field to alpha (XCOURSE_NUMBER) (the lower case c in "D8c" is very important, it removes the thousands comma), then left-justify the converted field (ZCOURSE_NUMBER).
 
Report This Post
<new2focus>
posted
Thanks a lot Pietro.. You have a gr8 weekend!
 
Report This Post
Platinum Member
posted Hide Post
IBI is suppose to be working a new feature where you can get the individual values within the bar. Plus the total at the top.

Lenny
 
Posts: 104 | Location: Boston | Registered: April 23, 2003Report 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     How to concatinate values from rows into one

Copyright © 1996-2020 Information Builders