June 17, 2008, 09:51 AM
Francis MarianiCount of rows as a column in the hold file
I would like to have the number of rows in a hold file as a column in the same hold file. I've tried several different things with no success. This is what I finally came up with, running a query against the hold file to determine the number of rows. Does anyone have a better way?
TABLE FILE MSCRM_ACTIVITYPOINTER
SUM
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
COMPUTE CNT_ACTIVITY/D6 = 1;
BY PARTYID
WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;
ON TABLE HOLD AS H001M1
END
-RUN
TABLE FILE H001M1
SUM
CNT_ACTIVITY
SUM
PARTYIDNAME
ACTIVITYTYPECODE
BY PARTYID
ON TABLE HOLD AS H001M1
END
Thanks,
Scott
June 17, 2008, 10:16 AM
GamPI think the compute always comes up with 1.
Might be better to do a count as your first verb request, such as:
TABLE FILE ...
SUM
CNT.ACTIVITYTYPECODE
SUM
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
BY PARTYID
WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;
ON TABLE HOLD AS H001M1
END
Hope this helps ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
June 17, 2008, 10:16 AM
GlendaIs this what you are wanting?
CODE]
TABLE FILE CAR
COUNT COUNTRY
MODEL
PRINT MIN.COUNTRY
MIN.MODEL
BY COUNTRY
ON TABLE HOLD AS CCNT
END
TABLE FILE CCNT
PRINT *
END
-EXIT[/CODE]
Glenda
In FOCUS Since 1990
Production 8.2 Windows
June 17, 2008, 10:21 AM
Danny-SRLFrancis,
If you have a hierarchy type file (like CAR) this will work:
-* File franciscount.fex
DEFINE FILE CAR
ONE/I5 WITH CAR = 1;
END
TABLE FILE CAR
SUM ONE
SUM SALES
BY COUNTRY BY CAR
END
Aside from that, still thinking...
Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 17, 2008, 10:23 AM
Francis MarianiT.S.
Thanks for the response.
I originally coded the second option you suggest.
I can't use the first option you suggest, because the HOLD file is subsequently used in an inner JOIN to a SQL table and the CNT_ACTIVITY column will not sum up to the original count because sometimes there is no matching row in the cross-referenced file.
Cheers,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 17, 2008, 10:25 AM
Francis MarianiI can't use a COUNT because there may be more than one input row - the COUNT is thus more than the number of output rows.
The program reads MS SQL Server tables.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 17, 2008, 10:30 AM
Danny-SRLFrancis,
TABLE FILE CAR
SUM SALES
COMPUTE MCNT/I5=1 + MCNT;
BY SEATS
ON TABLE HOLD
END
TABLE FILE HOLD
SUM MAX.MCNT
SUM SALES
BY SEATS
END
Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 17, 2008, 10:32 AM
j.grossIt can be done in one pass, even when each result row summarizes multiple source data rows:
TABLE FILE CAR
SUM CNT.DST.CAR WITHIN TABLE
CNT.MODEL
SALES
BY CAR
END
- Jack Gross
WF through 8.1.05
June 17, 2008, 11:09 AM
Darin LeeThere was a presentation given at Summit regarding the "magical" use of WITHIN that would probably give you a solution. I can't remember that name of the presentation or even who presented it - I'll have eto go back through my notes (that I left in my other office). JG's post using WITHIN TABLE reminded me of this.
TABLE FILE CAR
PRINT CAR
CNT.CAR WITHIN COUNTRY
CNT.CAR WITHIN TABLE
BY COUNTRY
END
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
June 17, 2008, 03:09 PM
Francis MarianiWITHIN does the trick! I'll have to read up about it.
Thanks a million!
TABLE FILE MSCRM_ACTIVITYPOINTER
SUM
CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_ACTIVITY
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
BY PARTYID
WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;
ON TABLE HOLD AS H001M1
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 17, 2008, 03:21 PM
Tony ADarin,
I think WITHIN was mentioned in both the "One Liners ... " and "FOCUS Internals ... "
T
June 17, 2008, 03:22 PM
Francis MarianiWITHIN is definitely a "One Liner" that opens doors...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 17, 2008, 03:23 PM
j.grossquote:
TABLE FILE MSCRM_ACTIVITYPOINTER
SUM
CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_ACTIVITY
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
BY PARTYID
. . .
WHERE ACTIVITYTYPECODE EQ 4201
. . .
END
Doesn't that yield all one's in the first column? I think you want
CNT.DST.
PARTYID WITHIN TABLE
- Jack Gross
WF through 8.1.05
June 17, 2008, 03:32 PM
Francis MarianiJack, the program returns 471 in the CNT_ACTIVITY column. I can't use CNT.DST.PARTYID because MS SQL Server does not allow prefixes for Uniqueidentifier columns which PartyID is.
Thanks,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 17, 2008, 03:42 PM
Darin LeeIt was the "One Liners" presentation. Didn't make it to the other one. See, I DID learn something there - it wasn't just an excuse for a great party.
Regards,
Darin
In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
June 17, 2008, 03:49 PM
Francis MarianiDigest this!
quote:
Manipulating Display Field Values in a Sort Group
You can use the WITHIN phrase to manipulate a display field’s values as they are aggregated
within a sort group. This technique can be used with a prefix operator to perform
calculations on a specific aggregate field rather than a report column. In contrast, the SUM
and COUNT commands aggregate an entire column.
You can use up to 64 fields in a display command when using the WITHIN phrase.The
WITHIN phrase requires a BY phrase and/or an ACROSS phrase. A maximum of two WITHIN
phrases can be used per display command. If one WITHIN phrase is used, it must act on a BY
phrase. If two WITHIN phrases are used, the first must act on a BY phrase and the second on
an ACROSS phrase.
You can also use WITHIN TABLE, which allows you to return the original value within a
request command. The WITHIN TABLE command can also be used when an ACROSS phrase
is needed without a BY phrase. Otherwise, a single WITHIN phrase requires a BY phrase.
- WebFOCUS - Creating Reports With WebFOCUS Language - Version 5 Release 3
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 17, 2008, 04:55 PM
j.grossI don't find that description of WITHIN clear. Whatever it is intended to mean, I don't think it's accurate -- I found SUM CNT.DST. and COUNT DST. yield identical results.
TABLE FILE CAR
"sum within table:"
SUM CNT.DST.SEATS WITHIN TABLE
SALES
BY CAR
END
TABLE FILE CAR
"count within table:"
COUNT DST.SEATS WITHIN TABLE
SUM.SALES
BY CAR
END
TABLE FILE CAR
"count within car"
COUNT DST.SEATS WITHIN CAR
SUM.SALES
BY CAR
ON TABLE SUMMARIZE
END
It's interstring semantics:
SUM CNT.DST.A WITHIN TABLE ... BY B
or
COUNT DST.A WITHIN TABLE ... BY B
means "take the Count of Distinct A within each value of B, summed up to the table level, and replicate in each row"
-- rather then "take the Count of Distinct A over the whole table, and replicate in each B row" as I had thought.
In other words, the verb object (including its prefix operators) is evaluated within sort groups, just as though there were no WITHIN specified; the WITHIN just alters the level of summation of that result, to be stored in each row of the result.
That's how you get 741 "distinct" values of ACTIVITYTYPECODE, when the underlying values all equal 4201.
- Jack Gross
WF through 8.1.05
June 18, 2008, 03:28 AM
Danny-SRLHi to all,
The weirdest thing about this syntax is the following:
TABLE FILE CAR
SUM CNT.DST.CAR WITHIN TABLE
SALES
BY CAR
BY MPG IN-GROUPS-OF 10
END
You think that this will count the distinct values of CAR, right? Well see the ouptut:
COUNT
DISTINCT
CAR MPG CAR SALES
--- --- -------- -----
ALFA ROMEO 20 12 30200
AUDI 20 12 7800
BMW 10 12 62540
20 12 17850
DATSUN 0 12 43000
JAGUAR 0 12 12000
10 12 0
JENSEN 10 12 0
MASERATI 0 12 0
PEUGEOT 20 12 0
TOYOTA 20 12 35030
TRIUMPH 20 12 0
It counts the 12 LINES and not the 10 CARs. So this is very good for you Francis, but I wonder whether somebody will not "correct" this feature in a future release...
Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF