hi all

I'd like to create a filter with values from a table. for this example I used a querry against the CAR table:

DEFINE FILE CAR
MODELSHORT/A3	= EDIT(MODEL,'999');
END

TABLE FILE CAR
BY BODYTYPE
BY MODELSHORT
WHERE SEATS NE '5'
END

gives the following output:


BODYTYPE MODELSHORT
CONVERTIBLE V12
COUPE 200
DOR
HARDTOP TR7
ROADSTER 200
SEDAN 200
B21
COR
INT



but I would like to achive this:


CONVERTIBLE 'V12'
COUPE '200','DOR'
HARDTOP 'TR7'
ROADSTER '200'
SEDAN '200','B21','COR','INT'



-*This should be used later as a filter in a loop. IN THIS CASE 5 TIMES

 
-SET &LOOPTIMES = &LINES;
 
 
-****************************************************************  LOOP
 
-SET &LOOPNO	= '1';
-REPEAT :LOOP4FILTERS &LOOPTIMES TIMES;
 

TABLE FILE DIFFERENT_TABLE
PRINT *
WHERE MODELSHORT IN '&FILTER';
ON TABLE HOLD FORMAT XLSX
END
-RUN

-SET &LOOPNO	= &LOOPNO+1;
-:LOOP4FILTERS

any ideas how I can get this done is highly appreciated.

many thanks

chris

This message has been edited. Last edited by: <Emily McAllister>,

May not be the only way to perform, but it works and we don't have all your needs, so the solution may not be the best one according to the needs.

DEFINE FILE CAR
MODELSHORT/A3	= EDIT(MODEL,'999');
END
TABLE FILE CAR
BY BODYTYPE
BY TOTAL COMPUTE ID/I2 = IF BODYTYPE EQ LAST BODYTYPE THEN LAST ID ELSE ID + 1;
BY MODELSHORT
WHERE SEATS NE '5';
ON TABLE HOLD AS FILTDATA FORMAT FOCUS
END
-RUN

TABLE FILE FILTDATA
SUM MAX.ID AS 'MAXID'
ON TABLE HOLD AS IDMAX FORMAT BINARY
END
-RUN

-READFILE IDMAX
-RUN
-SET &LOOPTIMES = &MAXID;
-SET &LOOPNO	= '1';

-REPEAT :LOOP4FILTERS &LOOPTIMES TIMES;
TABLE FILE FILTDATA
BY MODELSHORT
WHERE ID EQ &LOOPNO;
ON TABLE HOLD AS FLT FORMAT ALPHA
END
-RUN
 
DEFINE FILE CAR
MODELSHORT/A3	= EDIT(MODEL,'999');
END
TABLE FILE CAR
PRINT MODEL
WHERE MODELSHORT IN FILE 'FLT';
-*ON TABLE HOLD FORMAT XLSX
END
-RUN

-SET &LOOPNO = &LOOPNO + 1;
-:LOOP4FILTERS

Hi MartinY

Thank you for your answer.

But I do not get the result as needed:

quote:

'200','B21','COR','INT'

but I think I will go for an other approach with some joins.

thx

The basics, I'll leave you to read the values in to use within your filters -

DEFINE FILE CAR
  MODELSHORT/A3	= EDIT(MODEL,'999');
END

TABLE FILE CAR
  SUM COMPUTE MODELS/A30V = IF BODYTYPE NE LAST BODYTYPE THEN ''''||MODELSHORT||'''' ELSE LAST MODELS || ','''||MODELSHORT||'''';
   BY BODYTYPE
   BY MODELSHORT NOPRINT
WHERE SEATS NE '5';
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.MODELS
   BY BODYTYPE
END
-RUN

T

quote:

But I do not get the result as needed: quote:'200','B21','COR','INT'

With Tony sample, you will have it you wish but you need to define a field with a specific length that may not be enough except if you are sure of the maximum number of element that will be included in the filter field where also, you have to add 2 more digit for each value due to the quotes and 1 for the coma.

Using the sample that I gave you with a IN FILE, you don't really mind about the field length. The IN FILE can manage/include thousand of different values that only takes their length (no additional space for quotes or coma), which I think it seems to be less work to manage and more flexible.

But finally it's your call, we're just suggesting solutions.

quote:

define a field with a specific length

You can often get away with using a variable length field but need to be aware of the held format of this type of field so that you do not fall foul of it!

T

But still, even a variable field length format it's a specific length anyway
and as to be manage to

quote:

not fall foul of it!

But we're all saying the same thing

Another suggestion:

  
-* File WFT01.fex
DEFINE FILE CAR
MODSHORT/A6='''' || EDIT(MODEL,'999') || ''',';
END
TABLE FILE CAR
BY BODYTYPE
RANKED BY MODSHORT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD
END
TABLE FILE HOLD
SUM MAX.RANK
ON TABLE HOLD AS HRANK
END
-RUN
-READFILE HRANK
-SET &W=&RANK * 6;
-CLOSE HRANK
-RUN
TABLE FILE HOLD
SUM MODSHORT 
BY BODYTYPE
ACROSS RANK
ON TABLE SET NODATA ' '
ON TABLE SAVE
END
-RUN
-SET &L=&LINES;
-REPEAT #FILTER &L TIMES;
-READ SAVE &BODYT.A12. &MOD.A&W.EVAL.
-* For some reason TRIM doesn't work when there are trailing blanks, so:
-SET &RMOD=RJUST(&W,&MOD,'A&W.EVAL');
-SET &TMOD=TRIM('T',&RMOD,&W,',',1,'A&W.EVAL');
-SET &MODS=LJUST(&W,&TMOD,'A&W.EVAL');
-TYPE &BODYT &MODS
-#FILTER