Hello
I need to sum up up to 12 rows for a single custmer into 1 row with 12 columns for the same customer. How can I do it? I know that ACROSS can do it but I need to put the result on HOLD as proceed with further development.

I tryied do the following:
COMPUTE V_1/A09 MISSING ON = IF SSS EQ 'A01' THEN 'AAA';

COMPUTE V_2/A09 MISSING ON = IF SSS EQ 'A02' THEN 'BBB';
....................
COMPUTE V_12/A09 MISSING ON = IF SSS EQ 'A12' THEN 'A12';

Aand after SUM it by customer. But it does not work.

Any help would be appriciated

Thank you

When you say it doesn't work...are you getting an error? Is the SUM really after the COMPUTE?
If so...it should be at the beggining of the FEX.

If you are going to SUM alpha fields, you are only going to get the last one in the sort group. Please give us more detail.

If you could open the source of the whole fex that would be helpful so we can see the placement of some of your computes or sums.

I guess Mila has left the building

Sorry, was not here for some time.
Ginny
yes, I sum up alpha fields and get the last one.
I would like to get summary of them.
For this I tried to define alpha field with missing attribute.
I'm trying to achive the following. For example, for one customer I have 3 rows:

customer col1 col2 col3 col4

Mike AAA null null null
Mike null BBB null null
Mile null null null DDD

After SUM I would like to get the following:

Mike AAA BBB null DDD

The code:
COMPUTE COL1/A3 WITH MISSING= IF S1 EQ 'AAA' THEN
'AAA' ELSE NULL (?);
COMPUTE COL2/A3 WITH MISSING= IF S2 EQ 'BBB' THEN
'BBB' ELSE NULL (?);
COMPUTE COL3/A3 WITH MISSING= IF S3 EQ 'CCC' THEN
'CCC' ELSE NULL (?);
COMPUTE COL4/A3 WITH MISSING= IF S4 EQ 'DDD' THEN
'DDD' ELSE NULL (?);

........
SUM
COL1 COL2 COL3 COL4
BY CUSTOMER

Thank you all for your time
Mila

Well, Mila, that makes more sense. What is happening with the code you have written is that by the time you get to Mike's third record, you have wiped out the results you got from the previous two records.

And I would suggest that while you are testing, you change the SUM to PRINT so that you can see what is happening on each record.

You will have to do something like this:

COMPUTE COL1/A3 WITH MISSING=IF CUSTOMER NE LAST CUSTOMER AND S1 EQ 'AAA' THEN S1 ELSE
IF CUSTOMER EQ LAST CUSTOMER AND S1 IS MISSING THEN COL1 ELSE S1;

Do this for all 4 columns. You might have to play around with this code a bit as I haven't tested it but it should give you an idea of what you have to do. The goal is that once you get a column that is not null, you have to carry that value forward to the last record for the CUSTOMER so that when you SUM, you get all the values that you need.

Hope that makes sense to you.

Why not create a counter and reset it when ever the customer changes or the counter reaches max.

  
CNTR/I2 = IF CUSTOMER NE LAST CUSTOMER THEN 1 ELSE IF LAST CNTR EQ 12 THEN 1 ELSE LAST CNTR + 1;

COL1/A3 = IF CNTR EQ 1 THEN 'AAA' ELSE LAST COL1;

COL2/A3 = IF CNTR LT 2 THEN MISSING ELSE IF CNTR EQ 2 THEN 'BBB' ELSE LAST COL2;

COL3/A3 = IF CNTR LT 3 THEN MISSING ELSE IF CNTR EQ 3 THEN 'CCC' ELSE LAST COL3;

...
...

SUM COL1 COL2 COL3
BY CUSTOMER

...
... 

Hope this helps

Scott,

I don't think that will work. That assumes that COL1's value will always be on the first record for the customer, etc. The values could all be on the same record.

Ginny, Yes your right I did not look at the example I see that there are only three rows for with values for col1, col2, and col4 not col3.

Thank you very much. It seems that I still can not assign NULL to a computational field. It fails.

I believe I need to take a step back.
I would like to achieve the following:

I have a number of customers and they have a number of services S1...... S10. The set of services is predefined as otherwise would be crazy.
Customers have different sets of services. Initial extract (for example) contains the following:
Mike s1
Mike s4
Mike s10
Steve s5
Steve s10
Bob s1
Bob s2
Bob s6
Bob s9

How to come to the records like:
mike s1,s4,s10
Steve s5,s10
Bob s1,s2,s6,s9

Should I read the file sequentially?
Can I do it within table?

Would appreciate any input
Mila

Try ACROSS SERVICE.

Also please post the master for the extract file.

Thank you Ginny

Here is the layout:

0 FIELDNAME ALIAS FORMAT LENGTH
CUSTOMER E01 A30 30
SERVICE E02 A9 9
TOTAL 39
TOTAL 39


I used ACROSS for final report only to display rows across the report.
I’m not sure how can I work with data further. I believe I must know how many columns would be created and their names. So, hard coding is unavoidable

Thank you, Mila

APP FI CUSTMAS DISK customer.mas
-RUN
-WRITE CUSTMAS FILE=CUSTOMER,SUFFIX=FIX
-WRITE CUSTMAS SEGNAME=CUST,SEGTYPE=S0
-WRITE CUSTMAS FIELDNAME=CUSTOMER,ALIAS=CUST,FORMAT=A30,$
-WRITE CUSTMAS FIELDNAME=SERVICE,ALIAS=SVC,FORMAT=A09,$
APP FI CUSTOMER DISK customer.FTM
-RUN
-WRITE CUSTOMER Mike                          s1
-WRITE CUSTOMER Mike                          s4
-WRITE CUSTOMER Mike                          s10
-WRITE CUSTOMER Steve                         s5
-WRITE CUSTOMER Steve                         s10
-WRITE CUSTOMER Bob                           s1
-WRITE CUSTOMER Bob                           s2
-WRITE CUSTOMER Bob                           s6
-WRITE CUSTOMER Bob                           s9
TABLE FILE CUSTOMER 
SUM
COMPUTE CUSTNO/I4=IF CUSTOMER NE LAST CUSTOMER THEN CUSTNO+1 ELSE CUSTNO;
        SVCNO/I4=IF CUSTOMER NE LAST CUSTOMER THEN 1 ELSE SVCNO+1;
BY CUSTOMER
BY SERVICE
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE HOLD
SUM SERVICE
BY CUSTNO NOPRINT
BY CUSTOMER
ACROSS SVCNO NOPRINT
END
 

Is this what you want?

Ginny

ACROSS works perfectly and creates the file I would like to get :
TABLE FILE HOLD_ALL_SERVICES
SUM
SERVICE
BY CUSTOMER
ACROSS SERVICE
ON TABLE SAVE AS SAVE
-*ON TABLE HOLD AS HOLD
END



but creates file which I can not further work with :

0 NUMBER OF RECORDS IN TABLE= 8 LINES= 8
0 NUMBER OF RECORDS IN TABLE= 8 LINES= 3
ALPHANUMERIC RECORD NAMED SAVE
0 FIELDNAME ALIAS FORMAT LENGTH
CUSTOMER E01 A30 30
SERVICE E02 A9 9
SERVICE E02 A9 9
SERVICE E02 A9 9
SERVICE E02 A9 9
SERVICE E02 A9 9
TOTAL 75
All the columns are named SERVICE

How to create a HOLD file which would be regular file?

Mila

Ginny

I've learned that when the table which creates ACROSS table is put on HOLD the WebFOCUS names the columns and creates a regular file.
So, I'm fine now
and thank you very much for your help.

Mila

Mila,

Look up the ASNAMES parameter. You can use it to alter the default names in the HOLD file.

Ginny, I use alias names.
I'm happy with the code. The only thing is that it has hard coding which I can not get around (I beleive) as to get all of them into single field I need to know how many of them there.

COMPUTE ALL_OF_THEM/A350 =
E11 | ' ' | E12 | ' ' | E13 | ' ' | E14 | ' ' | E15 | ' ' |
E16 | ' ' | E17 | ' ' | E18 | ' ' | E19 | ' ' | E20 | ' ' |
E21 | ' ' | E22 | ' ' | E23 | ' ' | E24 | ' ' | E25 | ' ' |
E26 | ' ' | E27 | ' ' | E28 | ' ' | E29 | ' ' | E30 | ' ' |
E31 | ' ' | E32 | ' ' | E33 | ' ' | E34 | ' ' | E35 | ' ' |
E36 | ' ' | E37 | ' ' | E38 | ' ' | E39 | ' ' | E40 | ' ' |
E41 | ' ' | E42 | ' ' | E43 | ' ' | E44 | ' ' | E45 ;

COMPUTE SERVICE_GROUP/A100 = SQUEEZ(350, ALL_OF_THEM,'A100');

Mila

Mila,

Check out this recent posting:

Recursive Concatenation

You wouldn't have to mess with a hold file if you use this technique. Make sure that your data is sorted by CUSTOMER.

Hi Mila,

This should do it for you. I wrote this for a WebFOCUS student of mine several years ago.

 
-*  This was created to solve a student's question of how to
-*  create a report showing all of a person's dependents on
-*  the same line.

-*  VIDEOTRK's data was used to simulate the real Social Security data,
-*  so go by the column titles on the report, not the data.

-*  To accomplish this task, 3 passes at the data were necessary.
-*  Note that when you run this you will get a total of three reports.
-*  When you close the report viewer, a report of the next pass will
-*  generate.  Compare each report to the corresponding section of
-*  the code.


DEFINE FILE VIDEOTRK
-* The first 5 fields create a simulated Social Security Number.
    INT_CUSTID/I4    = EDIT (CUSTID);
    DECML_CUSTID/D10 = INT_CUSTID / 77 * 15812347;
    INT2_CUSTID/I9   = DECML_CUSTID;
    ALPHA_CUSTID/A9  = EDIT (INT2_CUSTID);
    SSN/A11          = EDIT (ALPHA_CUSTID, '999-99-9999');
END

TABLE FILE VIDEOTRK
    HEADING CENTER
        "1ST PASS </1"
    PRINT
        SSN
        MOVIECODE
        COMPUTE DEPEND_NAME/A6 = IF   EDIT (SSN, '$$$$$$$$$$9') EQ '7'
                                 THEN ' '
                                 ELSE MOVIECODE;

-* The following field is the dependants flag field.
        COMPUTE DEPEND_FLAG/A3 = IF   DEPEND_NAME NE ' '
                                 THEN 'YES'
                                 ELSE 'NO';

    BY SSN
END

HOLD AS HOLD1


TABLE FILE HOLD1
    HEADING CENTER
        "2ND PASS </1"
    PRINT
        DEPEND_FLAG
-* The following counter field used to map individual field values
-* to an instance (record) number.
        COMPUTE COUNTER/I1     = IF   SSN EQ LAST SSN
                                 THEN COUNTER + 1
                                 ELSE 1;

        COMPUTE DEPENDANT_1/A6 = IF   COUNTER EQ 1
                                 THEN DEPEND_NAME
                                 ELSE ' ';
        COMPUTE DEPENDANT_2/A6 = IF   COUNTER EQ 2
                                 THEN DEPEND_NAME
                                 ELSE ' ';
        COMPUTE DEPENDANT_3/A6 = IF   COUNTER EQ 3
                                 THEN DEPEND_NAME
                                 ELSE ' ';
        COMPUTE DEPENDANT_4/A6 = IF   COUNTER EQ 4
                                 THEN DEPEND_NAME
                                 ELSE ' ';
     BY SSN
END

HOLD AS HOLD2

TABLE FILE HOLD2
    PRINT *
    BY SSN
    BY COUNTER
    ON TABLE HOLD AS HOLD3
END

DEFINE FILE HOLD3
    DEPENDANT_A/A6 = IF   COUNTER EQ 1
                     THEN DEPENDANT_1
                     ELSE LAST DEPENDANT_A;

    DEPENDANT_B/A6 = IF   (COUNTER EQ 2)
                     THEN DEPENDANT_2
                     ELSE
                     IF   COUNTER GT 2
                     THEN LAST DEPENDANT_B
                     ELSE ' ';

    DEPENDANT_C/A6 = IF   (COUNTER EQ 3) AND (SSN EQ LAST SSN)
                     THEN DEPENDANT_3
                     ELSE
                     IF   COUNTER GT 3
                     THEN LAST DEPENDANT_C
                     ELSE ' ';

    DEPENDANT_D/A6 = IF   COUNTER EQ 4
                     THEN DEPENDANT_4
                     ELSE ' ';
END
TABLE FILE HOLD3
    HEADING CENTER
        "3RD PASS </1"
    SUM
        DEPEND_FLAG
        DEPENDANT_A
        DEPENDANT_B
        DEPENDANT_C
        DEPENDANT_D
    BY SSN AS '    SSN    '
END

 

Regards,

John

Thank you very much for your help.
have a nice day, Mila