June 02, 2008, 11:20 AM
GinnyJakesScott,
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.
June 02, 2008, 11:38 AM
TexasStingrayGinny, 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.
Scott
June 02, 2008, 04:42 PM
MilaThank 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
WebFOCUS 7.1
UNIX
June 02, 2008, 05:53 PM
GinnyJakesTry ACROSS SERVICE.
Also please post the master for the extract file.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
June 03, 2008, 10:27 AM
MilaThank 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
WebFOCUS 7.1
UNIX
June 03, 2008, 11:13 AM
GinnyJakesAPP 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
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
June 03, 2008, 12:09 PM
MilaGinny
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
WebFOCUS 7.1
UNIX
June 03, 2008, 01:59 PM
MilaGinny
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
WebFOCUS 7.1
UNIX
June 03, 2008, 02:13 PM
GinnyJakesMila,
Look up the ASNAMES parameter. You can use it to alter the default names in the HOLD file.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
June 03, 2008, 04:09 PM
MilaGinny, 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
WebFOCUS 7.1
UNIX
June 03, 2008, 05:12 PM
GinnyJakesMila,
Check out this recent posting:
Recursive ConcatenationYou wouldn't have to mess with a hold file if you use this technique. Make sure that your data is sorted by CUSTOMER.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
June 03, 2008, 05:42 PM
JohnBHi 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
WF 7.7.03, Windows 7, HTML, Excel, PDF
June 04, 2008, 11:19 AM
MilaThank you very much for your help.
have a nice day, Mila
WebFOCUS 7.1
UNIX