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,

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,

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.

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


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

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.

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

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

Hey, thanks everyone for chiming in! Lots of great options here. Appreciate each of your time and help.

- CoolGuy