I'm looking for help in producing a report to count and group or role up by "titles" see figure2.

What I need is a DEFINE statement or your suggestion to exclude in -figure2- any value before "(oc:" result (Escalation, Follow-up Call) grouped.

Currently the report is counting each field as a different value because of the names after "(oc:" =our client.


eXAMPLE: -figure1-
TABLE FILE DATABASE1
SUM
CNT.TITLE AS 'COUNT'
BY
TITLE AS 'TITLE NAME'
ON TABLE SUBHEAD


current rEPORT-EXAMPLE with TITLES: -figure2-

Escalation (oc: Jim williams) 1
Escalation (oc: Mike williams) 1
Escalation (oc: Mike John) 1
Escalation (oc: Michael williams) 1
Escalation (oc: Mike Bond) 1
Escalation (oc: williams smith) 1
Escalation (oc: Mike jones) 1
Escalation (oc: Mike schott)
Escalation (oc: Mike lee)
Escalation (oc: jones Wong)
Follow-up Call (oc: Mike Bond)
Follow-up Call (oc: Mike williams)
Follow-up Call (oc: Mike jones)
Follow-up Call (oc: Mike williams)
Follow-up Call (oc: Mike williams)
Follow-up Call (oc: Mike williams)
Follow-up Call (oc: Mike lee)
Follow-up Call (oc: Mike williams)


Should be;
Escalation 12 (total value)
Follow-up Call 17 (total value)

you might want to try something like:

DEFINE FILE DATABASE1
SHORT_TTL/A20 = GETTOK(TITLE,40,1,'(',20,'A20');
END
TABLE FILE DATABASE1
WRITE CNT.TITLE
BY SHORT_TTL
...
END

You might want to look up GETTOK to make sure you understand its use and change the 40 to whatever the length of TITLE is in your file.

hth,

drew

If I understand what you are asking for you want l out everything before the (oc: and put it in a new field to then sort/group on.

Use GETTOK.

DEFINE FILE...
OUTFIELD/A? = GETTOK(infield, inlen, token_number, 'delim', outlen, OUTFIELD)

Where
- infield is your field name
- inlen is the length of the input field
- delim is ' ('
- outlen is the length of the longest token(string) you will extract
- outfield is your defined field that you are putting this result into.

quote:

Originally posted by drew billingslea:
[qb] you might want to try something like:

DEFINE FILE DATABASE1
SHORT_TTL/A20 = GETTOK(TITLE,40,1,'(',20,'A20');
END
TABLE FILE DATABASE1
WRITE CNT.TITLE
BY SHORT_TTL
...
END

You might want to look up GETTOK to make sure you understand its use and change the 40 to whatever the length of TITLE is in your file.

hth,

drew [/qb]

Thanks I'll try this ... great help

quote:

Originally posted by Piipster:
[qb] If I understand what you are asking for you want l out everything before the (oc: and put it in a new field to then sort/group on.

Use GETTOK.

DEFINE FILE...
OUTFIELD/A? = GETTOK(infield, inlen, token_number, 'delim', outlen, OUTFIELD)

Where
- infield is your field name
- inlen is the length of the input field
- delim is ' ('
- outlen is the length of the longest token(string) you will extract
- outfield is your defined field that you are putting this result into. [/qb]

Great reply ... you answered my question ...

As a new user this is very helpful

A big thanks ... I'll try tomorrow

Works great however can I use multiply

??? about multi 'delim' ???

DEFINE FILE...
OUTFIELD/A? = GETTOK(infield, inlen, token_number, 'delim', outlen, OUTFIELD)
OUTFIELD/A? = GETTOK(infield, inlen, token_number, 'delim', outlen, OUTFIELD)


role into one output file
Where
- infield is your field name
- inlen is the length of the input field
- delim is ' (' and � �� and '+'
- outlen is the length of the longest token(string) you will extract
- outfield is your defined field that you are putting this result into.

I'm not quite sure what you are asking, but I'll take a stab at it.

You can use the GETTOK against the same field more than once to break it into multiple pieces.

for example
Melbourne Vic AU

If I have an address line, like the one above I can break it into three pieces by looking for the ' '. Token '1' would give me everything before the first space and token 2 gives me everything after it. Apply the GETTOK again to the second part.

As far as I know the delimiter can be almost anything. You'll note that in my first example I was actually looking for a space and the ( not just the (.

Hope that helps.

two ideas: (off the top of my head)

1) use posit and substr like:

-* if POSIT does not find the string it
-* returns 0
CHK1/I2 = POSIT(TITLE,40,'(',1,'I2');
CHK2/I2 = POSIT(TITLE,40,'-',1,'I2');
CHK3/I2 = POSIT(TITLE,40,'+',1,'I2');
-* decide which to use
CDELM/I2 = IF CHK1 GT 0 THEN CHK1 ELSE
IF CHK2 GT 0 THEN CHK2 ELSE
IF CHK3 GT 0 THEN CHK3 ELSE 40;
-* now go get it
OTHR_TTL/A40 = SUBSTR(40,TITLE,1,CDELM,CDELM,'A40');


2) use multiple gettoks and select the one to use like:

-* gettok on the 3 delimeters
T1VAL/A40 = GETTOK(TITLE,40,1,'(',40,'A40');
T2VAL/A40 = GETTOK(TITLE,40,1,'-',40,'A40');
T3VAL/A40 = GETTOK(TITLE,40,1,'+',40,'A40');
-* now see which one is different
-* not finding a token returns the original string
OTHR_TTL/A40 = IF T1VAL NE TITLE THEN T1VAL ELSE
IF T2VAL NE TITLE THEN T2VAL ELSE
IF T3VAL NE TITLE THEN T3VAL ELSE
TITLE;


I like 1 more than 2 because it doesn't create a bunch of A40 fields.

hth,

drew