[CLOSED]table file - transponse rows to collums, reformating and use it as filter
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
chrisThis message has been edited. Last edited by: <Emily McAllister>,
WebFOCUS 8.1.0.5 (Server + Client) Windows 2012 R2 BW, Oracle, Excel, PDF, HTML
November 06, 2015, 07:44 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 06, 2015, 08:38 AM
WFtESTER
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
WebFOCUS 8.1.0.5 (Server + Client) Windows 2012 R2 BW, Oracle, Excel, PDF, HTML
November 06, 2015, 09:35 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
November 06, 2015, 10:06 AM
MartinY
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.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 06, 2015, 10:15 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
November 06, 2015, 10:31 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 08, 2015, 10:41 AM
Danny-SRL
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
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF