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     [SOLVED] Easy way to create a com-delim string of a fields values stored in a DM var?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Easy way to create a com-delim string of a fields values stored in a DM var?
 Login/Join
 
Virtuoso
posted
Hi all,

Let's say we're working with the CAR file and we've got the CAR field that stores all the values for the different cars in the table. Is there a way to easily iterate through that field's values, creating a comma delimited string of each value all stored in a Dialog Manager variable?

Any examples/ideas would be helpful. Thanks!

Wish there was a CONCATFIELDVALS() that lets you specify a field and a delimiter...

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Master
posted Hide Post
Why do you need is as DM Variable? Are you planning on filtering with the value? What is youir end goal on this one?


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
Eric,

Sorry to not include that bit of info. My end goal is to filter on a list of store numbers by sticking the comma delimited list into a WHERE clause that uses the IN() function.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Virtuoso
posted Hide Post
HI CoolGuy

Here is one way to accomplish it.


-DEFAULTH &CAR='';
TABLE FILE CAR
BY CAR
ON TABLE HOLD AS CARS FORMAT ALPHA
END
-RUN
-STARTLOOP
-SET &CNTR = 0;
-READLOOP
-READFILE CARS
-IF &IORETURN NE 0 GOTO ENDLOOP;
-SET &CAR = TRUNCATE(&CAR);
-IF &CNTR GT 0 THEN GOTO MOREFILES ;
-SET &CARS = '&CAR.EVAL';
-GOTO SETCNTR
-MOREFILES
-SET &CARS = &CARS | ',' | '&CAR.EVAL';
-SETCNTR
-SET &CNTR = &CNTR + 1;
-IF &CNTR GE &LINES GOTO ENDLOOP ;
-GOTO READLOOP
-ENDLOOP
-TYPE &CARS


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Master
posted Hide Post
If you are able to use SQL you could use the LISTAGG([field],'delimiter') function. Or filter directly against the file.

SET HOLDLIST = PRINTONLY

TABLE FILE CAR
SUM COUNTRY NOPRINT
BY COUNTRY 
WHERE COUNTRY IN ('ENGLAND','FRANCE')
ON TABLE SAVE AS MY_FLTR
END

TABLE FILE CAR
SUM RETAIL_COST SALES DEALER_COST
BY COUNTRY 
BY CAR
IF COUNTRY EQ (MY_FLTR)
END



there is a limitation to the number of values that you can filter this way. I think its some where around 1k. Although it might be a character limitation and not a number of values limitation


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
Also you could use PUTDDREC to write to a flat file and then include the flat file.

I prefer saving the file file and filtering against it though as long as the number of values are relatively limited.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Platinum Member
posted Hide Post
This creates a string of countries, one with quotes and one without ...

-DEFAULTH &VAL_STR1 = ''
-DEFAULTH &VAL_STR2 = ''

TABLE FILE CAR
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR1/A100V = LAST VAL_STR1 || COMMA || LJUST(12, COUNTRY, 'A12V');
      COMPUTE VAL_STR2/A100V = LAST VAL_STR2 || COMMA || '''' | LJUST(12, COUNTRY, 'A12V') || '''';
   BY COUNTRY NOPRINT
ON TABLE HOLD AS TEMPHLD1
END
-RUN
TABLE FILE TEMPHLD1
  SUM LST.VAL_STR1 LST.VAL_STR2
ON TABLE HOLD AS TEMPHLD2
END
-RUN
-READFILE TEMPHLD2
-SET VAL_STR1 = TRUNCATE(&VAL_STR1);
-SET VAL_STR2 = TRUNCATE(&VAL_STR2);
-TYPE &|VAL_STR1 = &VAL_STR1
-TYPE &|VAL_STR2 = &VAL_STR2


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Platinum Member
posted Hide Post
Plenty way skinning cat!! Just be how you are liking it skinned Smiler

Note: no animal is being hurt in this production Wink

-DEFAULTH &DATAOUT = ''
FILEDEF TEMPHLD1 DISK HOLDFILE.FTM
-RUN
-* alternate synonym being for -READFILE - change as is being need
EX -LINES * EDAPUT MASTER,TEMPHLD1,CV,MEM
FILE=TEMPHLD1, SUFFIX=FIX , $
SEGMENT=TEMPHLD1, SEGTYPE=S0, $
FIELD=DATAOUT,  ,A100   ,A100   ,$
EDAPUT*
-RUN

TABLE FILE CAR
SUM FST.COUNTRY
ACROSS COUNTRY NOPRINT
ON TABLE SAVE AS HOLDFILE FORMAT DFIX DELIMITER , ENCLOSURE '''' HEADER NO
END
-RUN

-READFILE TEMPHLD1

-TYPE &|DATAOUT = &DATAOUT


Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report This Post
Virtuoso
posted Hide Post
Hey, thanks everyone for chiming in! Lots of great options here. Appreciate each of your time and help.

- CoolGuy


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report 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     [SOLVED] Easy way to create a com-delim string of a fields values stored in a DM var?

Copyright © 1996-2020 Information Builders