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