Hello,

Newbie to the forums. I need to create a DEFINE based on each column of a result set. However, I don't know the number of columns each row will have, as it will be dynamic (up to 30 columns) based on an ACROSS containing 1-30 per row. I'm wondering if there is an easy loop function I could use.

I don't have access to the CAR file, so I'm hoping my code makes sense. I can give more detailed examples if needed. I've used the LIST function to list out each data point in its own column.

 
TABLE FILE CAR
BY  LOWEST MODEL
BY  LOWEST CAR
ON TABLE HOLD AS PREP
END

TABLE FILE PREP
LIST MODEL
BY  LOWEST CAR
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT MODEL 
BY CAR
ACROSS LIST AS 'CAR_'
ON TABLE HOLD AS FNL
END

TABLE FILE FNL
PRINT *
BY  LOWEST MODEL
ON TABLE HOLD AS DFN
END

I would imagine the DEFINE would look something similar to the following if I knew the number of columns that each row had data for:

DEFINE FILE DFN
MODEL1/A50V=IF E03 EQ MISSING OR '' THEN '' ELSE E03;
MODEL2/A50V=IF E04 EQ MISSING OR '' THEN '' ELSE E04;
MODEL3/A50V=IF E05 EQ MISSING OR '' THEN '' ELSE E05;
...
...
...
MODEL 30/A50V=IF E32 EQ MISSING OR '' THEN '' ELSE E32;
END

Also, any thoughts on why I'm getting two columns of data for Model (e.g., it's repeating for both E01 and E02).

Thanks in advance for your help.

This message has been edited. Last edited by: FP Mod Chuck,

If you have the ASNAMES set to be ON when you do an across it will put the value of the across field at the end of the field name instead of needing to use the E## alias.

Can then check the fields that are in the hold file with

 ? HOLD FNL 

To be able to use them later. However if want to know how many of the 30 columns it makes without using the * to get all fields will need to do a loop and know what the max number is.

The following should reflect what would be needed if using the CAR file. I do change from using LIST to doing RANKED BY since I'm more familiar with using rank but should do the same thing.

SET ASNAMES = ON

TABLE FILE CAR
BY LOWEST CAR
RANKED AS 'RNK_MODEL' BY LOWEST MODEL
ON TABLE HOLD AS PREP
END

TABLE FILE PREP
BY RNK_MODEL
ON TABLE HOLD AS H_CNT
END
-SET &MODEL_CNT=&LINES;

-*Only doing AS 'MODEL_' to add the _ if don't need nor want that on your field do not need to change the field name as it will just add the across value at the end and be MODEL#
TABLE FILE PREP
PRINT MODEL AS 'MODEL_'
BY CAR
ACROSS RNK_MODEL
ON TABLE HOLD AS DFN
END

? HOLD DFN

-*Repeat just appends MODEL_ loop count to the end of the list each time. 
-*If had an across that had values from a table would need to READ/READFILE to know which value to append but with rank can just use the current loop number instead.
-SET &MODEL_FIELD_LIST='';
-REPEAT :LOOP_MODELS FOR &CNT FROM 1 TO &MODEL_CNT
-SET &MODEL_FIELD_LIST=&MODEL_FIELD_LIST | ' MODEL_&CNT.EVAL';
-:LOOP_MODELS

-*Will need to do SUM if you want it one line per CAR else will keep all of the original lines.
TABLE FILE DFN
SUM
&MODEL_FIELD_LIST
BY CAR
END

Also, kind of on a side note but one issue with having a dynamic list of fields is can't really do a MAX. or BY statement with them easy unless add a line for them within the repeat. that would look something like this.

-SET &MODEL_FIELD_LIST_BY=&MODEL_FIELD_LIST_BY | ' BY MODEL_&CNT.EVAL';
-SET &MODEL_FIELD_LIST_MAX=&MODEL_FIELD_LIST_MAX | ' MAX.MODEL_&CNT.EVAL';

Hi ScubaSteve,
I'm with you if you are an explorer of the deep!
But to the point. Your idea to use LIST is excellent. So maybe you are a "newbie" but you seem to have captured the FOCUS thought process.
Here is a small addendum:

  
-SET &ECHO=ALL;
-* File scubasteve01.fex
SET ASNAMES=ON
-* Find the max number of models
TABLE FILE CAR
SUM CNT.MODEL AS MODCNT
BY CAR
ON TABLE HOLD AS MODS
END
TABLE FILE MODS
SUM MAX.MODCNT
ON TABLE HOLD AS MAXMODS
END
-RUN
-READFILE MAXMODS
-TYPE &MODCNT
-CLOSE MAXMODS
-RUN
-* Use ScubaSteve's method to create columns of models per car
TABLE FILE CAR
BY  LOWEST CAR
BY  LOWEST MODEL
ON TABLE HOLD AS PREP
END
-RUN
TABLE FILE PREP
LIST MODEL
BY  LOWEST CAR
ON TABLE HOLD AS MODLIST
END
-RUN
TABLE FILE MODLIST
SUM MODEL AS MM
BY CAR
ACROSS LIST 
ON TABLE HOLD AS FNL
END
?FF FNL
-RUN
-* Dialog Manager provides the way to loop through the columns and create DEFINEs
DEFINE FILE FNL
-REPEAT #SCUBA FOR &I FROM 1 TO &MODCNT;
MODEL&I/A30=IF MM&I EQ '' OR MM&I IS MISSING THEN ' ' ELSE '&I-' | MM&I;
-#SCUBA
END
TABLE FILE FNL
PRINT 
-REPEAT #STEVE FOR &I FROM 1 TO &MODCNT;
MODEL&I
-#STEVE
BY CAR
END

Hi ScubaSteve

I see a couple of other folks have given you some ideas. I thought I'd try to answer your question about why you end up with two MODEL columns.

TABLE FILE CAR
BY LOWEST MODEL
BY LOWEST CAR
ON TABLE HOLD AS PREP
END

All WebFOCUS TABLE file commands have a verb like PRINT, LIST, SUM or COUNT to start off with. Since you didn't specify one, Internally, WF will use your first BY statement and change your request into:

TABLE FILE CAR
SUM
MODEL NOPRINT
BY LOWEST MODEL
BY LOWEST CAR
ON TABLE HOLD AS PREP
END

If you want that extra column to go away, you could try adding
TABLE FILE CAR
BY LOWEST MODEL
BY LOWEST CAR
ON TABLE HOLD AS PREP
ON TABLE SET HOLDLIST PRINTONLY
END

I think I've got that syntax right. You can check it out How to Control Fields in a HOLD File

Thats from 8105m that I noticed in your post. The idea is the same for all releases.

You're just running into FOCUS making your request fit it's standard idea of having a VERB and a VERB Object (it uses your first BY to do that).

Hope that helps.

Toby Mills, CISSP

Wow, thanks all for the great suggestions and help Crymsyn, Danny-SRL, and TobyMills! Your insights are great and super helpful.

I'm going to try this out and see what I come up with. I've got some other work priorities that are going to take some time, but I'll update this post once I finally get back into this report.

-Steve