Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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?
Go
New
Search
Notify
Tools
Reply
  
[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: 1105 | Location: USA | Registered: January 27, 2015Reply With QuoteReport 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, 2013Reply With QuoteReport 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: 1105 | Location: USA | Registered: January 27, 2015Reply With QuoteReport 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: 1500 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport 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, 2013Reply With QuoteReport 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, 2013Reply With QuoteReport 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: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport 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: 103 | Registered: April 06, 2009Reply With QuoteReport 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: 1105 | Location: USA | Registered: January 27, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.