The Master looks something like this:

FILE=STATION, SUFFIX=DB2, $
SEGNAME=STATION, SEGTYPE=S0, $
FIELD=STATION     , STATION     , I11, I4, $
FIELD=STATION_LVL2, STATION_LVL2, I11, I4, $
FIELD=STATION_LVL3, STATION_LVL3, I11, I4, $
FIELD=STATION_LVL4, STATION_LVL4, I11, I4, $
FIELD=STATION_LVL5, STATION_LVL5, I11, I4, $

Each STATION has one or more higher-level STATIONs. I'd like to determine the "parent" STATION by finding the first non-blank value from STATION_LVL2, STATION_LVL3, STATION_LVL4 and STATION_LVL5.

I can COMPUTE a column using IF ELSE IF syntax, but I was wondering if there was a quicker method, with a function perhaps, something like MAX(STATION_LVL2, STATION_LVL3, STATION_LVL4, STATION_LVL5), though, in this case, MAX will not give me what I need.

Any ideas?

Thanks,

This message has been edited. Last edited by: Kerry,

Would an alternate master with an OCCURS and counter field help you?

Yes, I think that would. Can I do that on a DB2 table or do I have to HOLD the DB2 table to format ALPHA or FOCUS first?

Thanks for the suggestion.

I think you have to hold it as alpha first. You can do your alt master on the fly and then apply it to the hold file.

OCCURS is an old FOCSAM construct. I find that it and alternate masters come in very handy to solve specific problems.

It is a lost art.

It is a lost art! I think I have my alternate Master with the OCCURS working (it's more complicated than the example I posted - I have four columns that require individual OCCURS).

What do I do next? I can print the values, but how do I determine which of the fields in the OCCURS is the first non-blank one?

Can you do a compute and while STATION is the same, set a flag or store the 'counter' field when you find the first one that is blank?

Or maybe you can print the 'counter' by the occur'd field. The one with spaces should float to the top.

I'm just making all this up out of my head. I think I may need to put some data together. Or maybe you can post some.

Alternate Master:

FILENAME=HSTATION_ALT, SUFFIX=FIX, $
SEGMENT=HSTATION, SEGTYPE=S0, $
  FIELDNAME=LVL14_STATION_ID , ALIAS=LVL14_STATION_ID , USAGE=I11, ACTUAL=A11, $
  FIELDNAME=LVL14_STATION    , ALIAS=LVL14_STATION    , USAGE=A05, ACTUAL=A05, $
  FIELDNAME=LVL14_STATION_NM , ALIAS=LVL14_STATION_NM , USAGE=A30, ACTUAL=A30, $
  FIELDNAME=LVL14_STATION_LVL, ALIAS=LVL14_STATION_LVL, USAGE=A02, ACTUAL=A02, $

  FIELDNAME=WNODE, ALIAS=WNODE_DATA, USAGE=A336, ACTUAL=A336, $

SEGNAME=WNODE, PARENT=HSTATION, POSITION=WNODE,  OCCURS=7, $
  FIELDNAME=WSTATION_ID , ALIAS=WSTATION_ID , USAGE=I11, ACTUAL=A11, $
  FIELDNAME=WSTATION    , ALIAS=WSTATION    , USAGE=A05, ACTUAL=A05, $
  FIELDNAME=WSTATION_NM , ALIAS=WSTATION_NM , USAGE=A30, ACTUAL=A30, $
  FIELDNAME=WSTATION_LVL, ALIAS=WSTATION_LVL, USAGE=A02, ACTUAL=A02, $

Fex:

FILEDEF HSTATION_ALT DISK baseapp/hstation.txt
-RUN
TABLE FILE HSTATION_ALT
PRINT

LVL14_STATION_ID  AS 'STATION_ID '
LVL14_STATION     AS 'STATION    '
LVL14_STATION_NM  AS 'STATION_NM '
LVL14_STATION_LVL AS 'STATION_LVL'

WSTATION_ID       AS 'PARENT_STATION_ID '
WSTATION          AS 'PARENT_STATION    '
WSTATION_NM       AS 'PARENT_STATION_NM '
WSTATION_LVL      AS 'PARENT_STATION_LVL'

-*BY LOWEST 1 WSTATION_LVL NOPRINT

-*WHERE WSTATION NE ''
END
-RUN

Removing the comment on the where statement will show you only those occurs that have data.
I thought sorting BY LOWEST 1 would work, but it doesn't.

Sorry about the wide data!

Data:

         42C0000DEFAULT H.U.B.                14.          .    .                             18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       8776U6477PCFS GROUP ADJUSTMENTS        35.          .    .                             42
         43C0001MONTANAS, SUCC. PRINC. DE     14      20492U1178CON MONTANAS (SUCC.PROPOSED 1)18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       8912U7131COLL. CENTER-SITES EST        35       8809U7013REGION CENTER-SITES DE ASF    42
         44C0002FIRST AMERICAN STATE ORLEANS  14      24778U6044FIRST AMERICAN STATE EASY     18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       8986U7205ORLEANS DOWNTOWN AREA         35       8821U7025GTA CENTRAL DISTRICT          42
         45C0003BARBADOS MAIN APPLES MAN      14      19549U3539BARBADOS M.O. EASY            18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       9213U7441BARBADOS CENTRAL AREA         35       8839U7043MONTANAS & NW CHICAGO DISTRICT42
         46C0004MAIN APPLES FRANCISCO BC      14      21426U0695MO FRANCISCO CONSOL           18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       9378U7611FRANCISCO DOWNTOWN AREA       35      56181U4992FRANCISCO CENTER DISTRICT     42

I'm working on this but a quick question: You want the first station that is not blank, correct?

Ginny,

Yes, for every LVL14_STATION row, I'd like the first non-blank WSTATION.

Thanks,

Here is what I have so far, pending your answer to my previous question.

APP FI HSTNMAS DISK hstation_alt.mas
-RUN
-WRITE HSTNMAS FILENAME=HSTATION_ALT, SUFFIX=FIX, $
-WRITE HSTNMAS SEGMENT=HSTATION, SEGTYPE=S0, $
-WRITE HSTNMAS   FIELDNAME=LVL14_STATION_ID , ALIAS=LVL14_STATION_ID , USAGE=I11, ACTUAL=A11, $
-WRITE HSTNMAS   FIELDNAME=LVL14_STATION    , ALIAS=LVL14_STATION    , USAGE=A05, ACTUAL=A05, $
-WRITE HSTNMAS   FIELDNAME=LVL14_STATION_NM , ALIAS=LVL14_STATION_NM , USAGE=A30, ACTUAL=A30, $
-WRITE HSTNMAS   FIELDNAME=LVL14_STATION_LVL, ALIAS=LVL14_STATION_LVL, USAGE=A02, ACTUAL=A02, $
-WRITE HSTNMAS SEGNAME=WNODE, PARENT=HSTATION,   OCCURS=7, $
-WRITE HSTNMAS   FIELDNAME=WSTATION_ID , ALIAS=WSTATION_ID , USAGE=I11, ACTUAL=A11, $
-WRITE HSTNMAS   FIELDNAME=WSTATION    , ALIAS=WSTATION    , USAGE=A05, ACTUAL=A05, $
-WRITE HSTNMAS   FIELDNAME=WSTATION_NM , ALIAS=WSTATION_NM , USAGE=A30, ACTUAL=A30, $
-WRITE HSTNMAS   FIELDNAME=WSTATION_LVL, ALIAS=WSTATION_LVL, USAGE=A02, ACTUAL=A02, $
-WRITE HSTNMAS   FIELDNAME=CNTR,         ALIAS=ORDER,        USAGE=I4,  ACTUAL=I4,  $

APP FI HSTATION_ALT DISK hstation_alt.ftm
-RUN
-WRITE HSTATION_ALT          42C0000DEFAULT H.U.B.                14.          .    .                             18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       8776U6477PCFS GROUP ADJUSTMENTS        35.          .    .                             42
-WRITE HSTATION_ALT          43C0001MONTANAS, SUCC. PRINC. DE     14      20492U1178CON MONTANAS (SUCC.PROPOSED 1)18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       8912U7131COLL. CENTER-SITES EST        35       8809U7013REGION CENTER-SITES DE ASF    42
-WRITE HSTATION_ALT          44C0002FIRST AMERICAN STATE ORLEANS  14      24778U6044FIRST AMERICAN STATE EASY     18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       8986U7205ORLEANS DOWNTOWN AREA         35       8821U7025GTA CENTRAL DISTRICT          42
-WRITE HSTATION_ALT          45C0003BARBADOS MAIN APPLES MAN      14      19549U3539BARBADOS M.O. EASY            18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       9213U7441BARBADOS CENTRAL AREA         35       8839U7043MONTANAS & NW CHICAGO DISTRICT42
-WRITE HSTATION_ALT          46C0004MAIN APPLES FRANCISCO BC      14      21426U0695MO FRANCISCO CONSOL           18.          .    .                             20.          .    .                             22.          .    .                             24.          .    .                             28       9378U7611FRANCISCO DOWNTOWN AREA       35      56181U4992FRANCISCO CENTER DISTRICT     42

TABLE FILE HSTATION_ALT
PRINT SEG.WSTATION_ID
BY LVL14_STATION_ID
BY LVL14_STATION
BY LVL14_STATION_NM
BY LVL14_STATION_LVL
BY CNTR
WHERE WSTATION_ID NE 0
END

I took your POSITION field out as you don't need it if the OCCURS are at the end of a record. I added an ORDER field in case it might be useful later on.

How about:

SUM
FST.WSTATION_ID       AS 'PARENT_STATION_ID '
FST.WSTATION          AS 'PARENT_STATION    '
FST.WSTATION_NM       AS 'PARENT_STATION_NM '
FST.WSTATION_LVL      AS 'PARENT_STATION_LVL'
BY LVL14_STATION_ID  AS 'STATION_ID '
BY LVL14_STATION     AS 'STATION    '
BY LVL14_STATION_NM  AS 'STATION_NM '
BY LVL14_STATION_LVL AS 'STATION_LVL'
WHERE WSTATION NE ' '

BTW,

  FIELDNAME=WNODE, ALIAS=WNODE_DATA, USAGE=A336, ACTUAL=A336, $

SEGNAME=WNODE, PARENT=HSTATION, POSITION=WNODE,  OCCURS=7, $

should work with DB2 also. I think I did something like that eons ago.

That works.

Ginny, Daniel,

That works!

Thanks a lot.

Francis,

I have a similar file (agent upline) where I was always having to code the "if" statement to find the highest agent level poplulated.

To accomplish this, I wrote an include that takes care of this and variablized it so that I could use it for other files where levels are used.

The include file is stored in baseapp and is called "upline.fex"

-SET &PASS    = &NFIELD + 1;
-REDO
-SET &PASS    = &PASS - 1;
-IF  &PASS    = 0 GOTO DOHFLD;
-SET &LNCT&PASS.EVAL = DECODE &PASS(&NFIELD.EVAL '&DFIELD.EVAL/A11 = IF (ARGLEN(11, &FNAME.EVAL&PASS.EVAL, ''I5'') GT 0) THEN &FNAME.EVAL&PASS.EVAL ELSE'
-                                   0 '-*'
-                                   1 '-*'
-                                   2 'IF (ARGLEN(11, &FNAME.EVAL&PASS.EVAL, ''I5'') GT 0) THEN &FNAME.EVAL&PASS.EVAL ELSE &FNAME.EVAL1;'
-                                ELSE 'IF (ARGLEN(11, &FNAME.EVAL&PASS.EVAL, ''I5'') GT 0) THEN &FNAME.EVAL&PASS.EVAL ELSE');
&LNCT&PASS.EVAL
-GOTO REDO
-DOHFLD

In my main fex I then do the following:

DEFINE FILE FILENAME
-SET &FNAME   = STATION_LVL;
-SET &NFIELD  = 5;
-SET &DFIELD  = PSTATION;
&&INCLUDE.EVAL upline  
END
-*
TABLE FILE FILENAME
PRINT
      *
      PSTATION

Of course you still need to update the MFD (or create a copy) so that the field fit the pattern below:

STATION_LVL1
STATION_LVL2
STATION_LVL3
STATION_LVL4
STATION_LVL5

Now I only ever have to make sure the field names are patterned properly and then type in 4 line in my main fex.

Funny how there are different ways to do things and still get the same outcome.

they're all integer, right?
so its zero you're looking for, yes?
DEFINE FILE X
S0/I1=STATION/STATION;
S1/I1=STATION_LVL1/STATION_LVL1;
S2/I1=STATION_LVL2/STATION_LVL2;
S3/I1=STATION_LVL3/STATION_LVL3;
S4/I1=STATION_LVL4/STATION_LVL4;
S5/I1=STATION_LVL5/STATION_LVL5;
ISSUM/I6=S5 + S4*10 + S3*100 + S2*1000 + S1*10000 + S0*100000;
ASUM/A6=EDIT(ISSUM);
WHERE/I1=POSIT(ASUM,6,'0',1,WHERE);
END

reduces your row to zeros and ones.
i so miss my economics days
we'ld take a time series vector
and create a matching vector of proxies wherever missing or 0 data existed.
sigh...
the days of actually having to think...