Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Condensing a one to many field into a single row
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Condensing a one to many field into a single row
 Login/Join
 
Member
posted
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
 
Posts: 5 | Registered: May 12, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: May 12, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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).
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: May 12, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you sort on keys such that there is one row instance per set of key values, the cardinality will be preserved.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Condensing a one to many field into a single row

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.