The following pipe creates the table and also handles nodes with missing dates.
data.1 = '01/21/09 NODE-A 1' data.2 = '01/21/09 NODE-A 2' data.3 = '01/21/09 NODE-C 3' data.4 = '01/22/09 NODE-A 4' data.5 = '01/22/09 NODE-B 5' data.6 = '01/22/09 NODE-C 6' data.0 = 6 'pipe (end ?) stem data.', '|fo:fanout', '|sort w2 7.2 1.2 4.2', /* sort by node and date */ '|lkup:lookup w1 w2 master detail', /* match on date */ '|join 1 / /', '|spec select second', ' d:w2 . n:w4 . x:w1 . c:w5 .', /* extract date, node, column index, count */ ' set #1+=c', /* accumulate count */ ' nowrite', /* no data to output yet */ ' break d', /* break when date changes */ ' print (#1; #1=0) (10*(x-1)+8, 8, r)', /* place count data in proper column */ ' break n', /* break when node changes */ ' id n 1 write', /* place node in record and write it */ '|fi:fanin 1 0', /* read column heading followed node data */ '|cons', '? fo:', '|chop before space', /* extract date */ '|sort unique 7.2 1.2 4.2', '|hdr:fanout', '|spec recno 1.2 1-* nw', /* add a date column index */ '|lkup:', '? hdr:', '|join * / /', /* create column heading */ '|spec 1-* 8', '|take 1', '|fi:' Using the supplied data the output is: NODE-A 3 4 NODE-B 5 NODE-C 3 6 James Johnson Mike Harding wrote:
As long as there aren't any holes in your data, the following should work. If there are holes, then logic to fill the gaps would be necessary. /* ** */ Address Command tkd.1 = '01/21/09 NODE-A count1' tkd.2 = '01/21/09 NODE-B count2' tkd.3 = '01/21/09 NODE-C count3' tkd.4 = '01/22/09 NODE-A count4' tkd.5 = '01/22/09 NODE-B count5' tkd.6 = '01/22/09 NODE-C count6' tkd.0 = 6 'PIPE (End ? Name Matricks)', ' Stem tkd.', '|Sort 7.2 a 1.2 a 4.2 a 10.6 a', '|dup:Fanout', '|Spec 1.8 1', '|Unique', '|Join * / /', '|Spec 1-* 9', '|twi:Fanin', '|Cons', /* or output of choice */ '?dup:', '|Sort 10.6 a 7.2 a 1.2 a 4.2 a', '|Spec w2 1.8 w3 n.8 right', '|Join keylen 8 / /', '|twi:' Exit Rc CMSTSO Pipelines Discussion List <CMS-PIPELINES@VM.MARIST.EDU> wrote on 01/22/2009 09:55:57 AM:I have a data file that has 3 fields, a date, a node and a count. I would like to build a matrix (table) with date across the top as column titles, nodes down the left column as row titles and counts filling the cells. The real data file has an unknown number of nodes but only 7 dates. I can build this in Rexx but would like to see if Pipelines can streamline the processing. Can someone point me at something? /Tom Kern Here is what the data would look like: 01/21/09 NODE-A count1 01/21/09 NODE-B count2 01/21/09 NODE-C count3 01/22/09 NODE-A count4 01/22/09 NODE-B count5 01/22/09 NODE-C count6 This is what I would like the output to look like: 01/21/09 01/22/09 NODE-A count1 count4 NODE-B count2 count5 NODE-C count3 count6
