[SOLVED] Easy way to create a com-delim string of a fields values stored in a DM var?
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.
November 06, 2017, 06:38 PM
eric.woerle
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
November 06, 2017, 06:46 PM
CoolGuy
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.
November 06, 2017, 06:55 PM
FP Mod Chuck
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
November 06, 2017, 06:58 PM
eric.woerle
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
November 06, 2017, 07:00 PM
eric.woerle
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
November 07, 2017, 09:00 AM
jfr99
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
November 07, 2017, 10:36 AM
Kofi
Plenty way skinning cat!! Just be how you are liking it skinned
Note: no animal is being hurt in this production
-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
November 07, 2017, 11:11 AM
CoolGuy
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.