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     Create amper variable from hold file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Create amper variable from hold file
 Login/Join
 
Platinum Member
posted
I have created a hold file that has a single column of data. I want to turn that column into an amper variable where each row is separated by a comma.

For Example:
 
TABLE FILE CAR
SUM
MAX.COUNTRY AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS HCOUNTRY FORMAT FOCUS INDEX COUNTRY
END 


Output:
COUNTRY
ALBANIA
CANADA
CUBA
E GERMANY
ENGLAND
FRANCE
GREECE
IRAN
IRAQ
ITALY
JAPAN
N KOREA
RUSSIA
S KOREA
W GERMANY

I want to make an amper variable from this list that looks like.

&COUNTRY = ALBANIA, CANADA, CUBA, E GERMANY, ENGLAND, FRANCE, GREECE.....

Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
WHERE COUNTRY EQ '&COUNTRY.(FIND COUNTRY IN HCOUNTRY ).Country';


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Platinum Member
posted Hide Post
quote:
'&COUNTRY.(FIND COUNTRY IN HCOUNTRY ).Country'


Can this be used in an IF statement?

Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
Here's one way:
  TABLE FILE CAR
    BY COUNTRY
    ON TABLE HOLD
  END
-RUN
-SET &N=&LINES;
-SET &NN=12*&N;
  DEFINE FILE HOLD
    I/I2=I+1;
    STRINGV/A&NN|V = IF (I EQ 1) THEN COUNTRY ELSE STRING || (', '| COUNTRY);
    STRING /A&NN   = STRINGV;
  END
  TABLE FILE HOLD
    WRITE LST.STRING
    ON TABLE SAVE
  END  
-RUN
-READ SAVE &STRING.A&NN.EVAL
-? &STRING


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Would it work the same if instead of countries you had a list of numbers (P7 format)?


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
You'd need to convert the values to alpha format in order to use the catenation operator. EDIT() will do that, if you don't mind the leading zeros (otherwise use FTOA and LJUST)

  
  DEFINE FILE CAR
    PACKED/P7=LENGTH;
  END
  TABLE FILE CAR
    BY PACKED
    ON TABLE HOLD
  END
-RUN
-SET &NN=(7+2)*&LINES-2;
  DEFINE FILE HOLD
    I/I2=I+1;
    STRING/A&NN|V = IF (I EQ 1) THEN EDIT(PACKED) ELSE STRING || (', '| EDIT(PACKED));
  END
  TABLE FILE HOLD
    WRITE LST.STRING/A&NN
    ON TABLE SAVE
	ON TABLE SET HOLDLIST PRINTONLY
  END  
-RUN
-READ SAVE &STRING.A&NN.EVAL
-? &STRING

results in
&STRING       = 0000163, 0000165, 0000170, 0000176, 0000177, 0000182, 0000187


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
I'm sorry...missed the part where you wanted it separated by comma's. Yes you could do mine with an IF. ...but guess you are on to what you really wanted.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
You could also just use a loop with a -READ.

TABLE FILE CAR
SUM
MAX.COUNTRY AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE SAVE AS HCOUNTRY FORMAT ALPHA
END
-RUN
-SET &COUNTER=&LINES - 1;
-READ HCOUNTRY &STRING.A10 NOCLOSE
-REPEAT READLIST &COUNTER.EVAL TIMES
-READ HCOUNTRY &VALUE.A10
-SET &STRING='&STRING.EVAL'||','||'&VALUE.EVAL';
-READLIST
-TYPE &STRING


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
You could also use the ACROSS verb to cut out concatenation after a repeated loop to read the &vars. If you use FORMAT COM then you only need to replace the double quotes by singles where necessary as the format will only add double quotes around alphanumeric values and not numeric. Less steps ....

You need to ensure that your resultant variable doesn't exceed limits for the IN statement.

APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM COUNTRY
ACROSS COUNTRY NOPRINT
ON TABLE SAVE AS MYHOLD FORMAT COM
END
-RUN
-* Make sure that this read is long enough
-READ MYHOLD &COUNTRY.A100
-RUN
-SET &COUNTRY = STRREP(&COUNTRY.LENGTH, '&COUNTRY.EVAL', 1, '"', 1, '''', &COUNTRY.LENGTH, 'A&COUNTRY.LENGTH');
TABLE FILE CAR
SUM RCOST DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
WHERE COUNTRY IN (&COUNTRY)
END
-RUN

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
Another possibility is to ensure that all values have quotes around them and a trailing comma. Then find the number of countries that answer screening conditions. Finally, as Tony suggests, use ACROSS for putting all values on one line. A finishing touch to remove the last comma.
  
-* File create_variable.fex
-* To prevent non-printing fields from being saved in the HOLD file 
SET HOLDLIST=PRINTONLY
-*
-* Add quotes and comma
DEFINE FILE CAR
QCOUNTRY/A13='''' | COUNTRY || ''',';
END
-*
-* Count the number of selected countries
TABLE FILE CAR
BY QCOUNTRY
WHERE SALES GT 15000;
ON TABLE HOLD
END
-RUN
-*
-* Calculate the full length of the saved line
-SET &QLEN=13 * &LINES;
-*
-* Save the line
TABLE FILE HOLD
SUM QCOUNTRY
ACROSS QCOUNTRY
ON TABLE SAVE
END
-RUN
-*
-* Read the saved values
-READ SAVE &COUNTRY.A&QLEN.EVAL
-*
-* Do away with the last comma
-SET &COUNTRY = &COUNTRY || ',';
-SET &COUNTRY = TRIM('T', &COUNTRY, &QLEN, ',', 1, A&QLEN.EVAL);
-*
-* Run the program
TABLE FILE CAR
SUM SALES
 BY COUNTRY
 BY CAR
 BY MODEL
WHERE COUNTRY IN (&COUNTRY)
END


The wonders of WF!


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
Bethany,

You have been given a lot of solutions, but what are you trying to accomplish? Why do you want the values in an amphere variable separated by commas?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
Here are two more solutions to consider:

This one use Define to build the quoted string and is suitable for feeding an SQL passthru

DEFINE FILE CAR
STRING_WK/A512V = IF LAST STRING EQ ' ' THEN '''' || COUNTRY || ''''
ELSE LAST STRING || ' , ''' || COUNTRY || '''';
END
TABLE FILE CAR
SUM
-** THIS COMPUTE IS NECCESARY TO REMOVE LENGTH IN VARIABLE FORMAT FIELD
COMPUTE STRING/A512 = STRING_WK;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS STROUT
END
-RUN

TABLE FILE CAR
PRINT
COUNTRY MODEL DCOST RCOST
WHERE COUNTRY IN (
-INCLUDE STROUT.FTM
)
END
-RUN

This one will work with a table request

TABLE FILE CAR
BY COUNTRY
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS STROUT
END
-RUN

TABLE FILE CAR
PRINT
COUNTRY MODEL DCOST RCOST
WHERE COUNTRY IN FILE STROUT
END
-RUN


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Report This Post
Platinum Member
posted Hide Post
Thanks for the many suggestions. I'm just getting back to working on this report and I'm still having problems. The big picture is for an FML report that needs to have particular formatting. Due to the way WHEN EXISTS works in FML, I need to determine whether a FOR value will exist in the final report and if not then skip the code for that particular FOR value.

This is the basic code I'm working with now.
This is to set the countries that I need in my output if they do exist;
-SET &COUNTRY1 = 'ENGLAND';
-SET &COUNTRY2 = 'ITALY';
-SET &COUNTRY3 = 'W GERMANY';
 

This would be the part where I get list of countries that meet a certain criteria and create the list of the ones that actually do exist for my final output.
TABLE FILE CAR
SUM
COUNTRY
    BY COUNTRY NOPRINT
	WHERE COUNTRY IN ('ENGLAND', 'ITALY');
   ON TABLE HOLD
END
-SET &N=&LINES;
-SET &NN=12*&N;
  DEFINE FILE HOLD
    I/I2=I+1;
    STRINGV/A&NN|V = IF (I EQ 1) THEN  '''' | COUNTRY | '''' ELSE STRING || (', '| '''' | COUNTRY || '''');
    STRING /A&NN   = STRINGV;
  END
  TABLE FILE HOLD
    WRITE LST.STRING
    ON TABLE SAVE
  END
-RUN
-READ SAVE &STRING.A&NN.EVAL

Here in the final output. I want to see if &COUNTRY1 (ENGLAND) is in the list of countries and if it's not then skip the code that would include it on the report. Then check to see if &COUNTRY2 is in the list etc...
  
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
FOR COUNTRY
-IF &COUNTRY1 NOT IN (&STRING) GOTO SKIP1;
ENGLAND OVER
-SKIP1
-IF &COUNTRY2 NOT IN (&STRING) GOTO SKIP2;
ITALY OVER
-SKIP2
-IF &COUNTRY3 NOT IN (&STRING) GOTO SKIP3;
W GERMANY OVER
-SKIP3
BAR
END  


I'm getting errors that "IN" must appear in a valid clause and if I don't get that message, it's still not correctly determining if &COUNTRY1,2,3 is actually in the list.

Thanks again for all of your suggestions.
Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
Didn't quite understand your big picture for what you are trying to accomplish, but aside from that - NOT IN is not valid syntax for what you are doing. NOT IN file is used to validate a value against a file and is used in a WHERE or IF statement within a TABLE command. It cannot be used to check a string or in Dialogue Manager.

You might try
-IF &STRING OMITS 'ENGLAND' or -IF &STRING NOT-LIKE %ENGLAND% which should both work in Dialogue Manager statements.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
Bethany,

If you have the list of values in a file then just read through that file using a repeat loop -

APP PREPENDPATH IBISAMP
TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY CONTAINS 'E'
ON TABLE SAVE AS CTRY_S1
END
-RUN
TABLE FILE CAR
SUM DEALER_COST
    RETAIL_COST
FOR COUNTRY
-READ CTRY_S1, &Country
-REPEAT :Loop WHILE &IORETURN EQ 0;
'&Country.EVAL' OVER
-READ CTRY_S1, &Country
-:Loop
""
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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     Create amper variable from hold file

Copyright © 1996-2020 Information Builders