[CLOSED] Condensing a one to many field into a single row

August 14, 2014, 02:37 PM

jbhurt@dst

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>,

August 14, 2014, 02:51 PM

j.gross

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

August 14, 2014, 02:59 PM

j.gross

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

August 14, 2014, 03:49 PM

jbhurt@dst

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?

August 14, 2014, 04:04 PM

j.gross

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).

August 14, 2014, 04:28 PM

jbhurt@dst

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.

August 14, 2014, 05:34 PM

j.gross

If you sort on keys such that there is one row instance per set of key values, the cardinality will be preserved.

August 15, 2014, 01:53 AM

Dave

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,

August 15, 2014, 11:50 AM

j.gross

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.

August 15, 2014, 12:10 PM

Alan B

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

August 18, 2014, 02:44 AM

Dave

Thanks Alan.

I finally got "CAR" to work again. It was broken since last migration.

That solves it.

August 18, 2014, 03:06 AM

Alan B

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

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

August 22, 2014, 09:25 AM

Danny-SRL

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