![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Member |
Hello all, I'm building a report that has a field that has a one to many relationship to the key field and I have a requirement that these values be condensed into a single row. It looks something like this: Original: Expected: Key Value Key Value A #foo A #foo, #bar A #bar B #foo, #bar, #foobar B #foo B #bar B #foobar I have tried to find a documented solution to this with no golden ticket. I did try to macgyver a similar solution for calculating a running total that works great for numbers but not so great for strings as seen below. DEFINE FILE MY_TABLE NEW_VALUE/A300V = IF KEY EQ LAST KEY THEN (NEW_VALUE || (', ' | VALUE)) ELSE VALUE; END As I'm sure you can tell, when I try to use this new field in my table, I just get a slowly building list of values instead of the ultimate value that I am wanting. I really just want the last of these new values associated with the key. I would be eternally grateful with any help on this.This message has been edited. Last edited by: <Kathryn Henning>, WebFOCUS 8.0 All Outputs Windows | ||
|
Virtuoso |
APP PREPENDPATH IBISAMP TABLE FILE CAR PRINT COMPUTE LIST/I5= IF COUNTRY EQ LAST COUNTRY THEN LAST LIST + 1 ELSE 1; BY COUNTRY BY CAR ON TABLE HOLD AS CAR1 END TABLE FILE CAR1 WRITE CAR BY COUNTRY ACROSS LIST NOPRINT END | |||
|
Virtuoso |
Or, APP PREPENDPATH IBISAMP TABLE FILE CAR LIST CAR BY COUNTRY BY CAR ON TABLE HOLD AS CAR1 END DEFINE FILE CAR1 CAR_LIST/A200V=IF LIST EQ 1 THEN CAR ELSE (LAST CAR_LIST) || (', ' | CAR); END TABLE FILE CAR1 WRITE LST.CAR_LIST AS 'CARS' BY COUNTRY END | |||
|
Member |
Thanks a lot! This worked great but I do have one question. Do you happen to know what the exact difference is between using WRITE vs PRINT? WebFOCUS 8.0 All Outputs Windows | |||
|
Virtuoso |
print does not aggregate -- it maps the incoming records 1-1 into the rows of the report. (Same for "list".) write (or sum or add, they're all synonymous) aggregates the records, so there is one row of outout for each distinct combination of sort-key values. (If there are no sort keys (no BY is present) a single row is produced: "there is only one empty set") The default aggregation operation is addition (SUM.) for additive columns (all numeric formats), and the Last incoming value (LST.) for non-additive columns(alphanumeric formats, and most forms of Date or DateTime). | |||
|
Member |
So would it be safe to assume that you could use WRITE to display one to one columns as well? If I understand this correctly, it would cause a hit to the efficiency of the report due to the LST operation occurring on all of columns, even those where the last value is the only value, but it would allow you to place any concatenated one to many columns among the one to one columns of the report. WebFOCUS 8.0 All Outputs Windows | |||
|
Virtuoso |
If you sort on keys such that there is one row instance per set of key values, the cardinality will be preserved. | |||
|
Master |
Or.... TABLE FILE MY_TABLE SUM COMPUTE NEWVALUE/A1000V = IF LAST KEY EQ KEY THEN NEWVALUE | ',' | VALUE ELSE VALUE; BY KEY END You were on the right track with that DEFINE.This message has been edited. Last edited by: Dave, _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
Virtuoso |
Dave -- NOT. The COMPUTE works off the internal matrix, which has only one row (hence only one value of VALUE) per value of KEY. Essentially, the presence of "VALUE" in the Compute expression causes VALUE to be added as a NOPRINT verb object, which will be treated as LST.VALUE, and NEWVALUE will just contain that one value. Try it: TABLE FILE IBISAMP/CAR SUM COMPUTE NEWVALUE/A1000V = IF LAST COUNTRY EQ COUNTRY THEN NEWVALUE | ',' | CAR ELSE CAR; BY COUNTRY END The answer set has no commas. - Jack Gross WF through 8.1.05 | |||
|
Virtuoso |
Where the key sort field may not be in sequence, then the hold phase can be removed by using: TABLE FILE CAR PRINT COMPUTE NEWVALUE/A1000V = IF LAST COUNTRY EQ COUNTRY THEN NEWVALUE | ',' | CAR ELSE CAR; BY COUNTRY BY TOTAL HIGHEST 1 NEWVALUE NOPRINT END Alan. WF 7.705/8.007 | |||
|
Master |
Thanks Alan. I finally got "CAR" to work again. It was broken since last migration. That solves it. _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
Virtuoso |
One interesting point came from this. The syntax: TABLE FILE CAR PRINT COMPUTE NEWVALUE/A1000V = IF LAST COUNTRY EQ COUNTRY THEN NEWVALUE | ',' | CAR ELSE CAR; BY COUNTRY BY TOTAL HIGHEST 1 NEWVALUE NOPRINT END v. TABLE FILE CAR BY COUNTRY BY TOTAL HIGHEST 1 COMPUTE NEWVALUE/A1000V = IF LAST COUNTRY EQ COUNTRY THEN NEWVALUE | ',' | CAR ELSE CAR; END In the second example, which is valid, the request brings in an implicit CAR, but the implied VERB is SUM not PRINT, and so does not work. It has to be coded as: TABLE FILE CAR PRINT CAR NOPRINT BY COUNTRY BY TOTAL HIGHEST 1 COMPUTE NEWVALUE/A1000V = IF LAST COUNTRY EQ COUNTRY THEN NEWVALUE | ',' | CAR ELSE CAR; END Alan. WF 7.705/8.007 | |||
|
Virtuoso |
jb, Do you need the commas? If you don't you could use: TABLE FILE filename LIST Value BY Key ON TABLE HOLD END TABLE FILE HOLD SUM Value BY Key ACROSS LIST NOPRINT END Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|