Count help

[#1]

Greetings,

I have a situation where I need to load a table and provide a column with the number of Rev-Trac# values found for each Rev-Trac# value in the table.

Example with count result (TX_Count): 

Rev-Trac #     TX_Version     TX_Transport     TX_Sequece     TX_Count

1111               2                         ABCK901222     10                    3

1111               3                         ABCK901225     20                    3

1111               4                         ABCK901239     30                    3

2222               2                         ABCK901255     10                    1

3333               2                         ABCK901251     20                    2

3333               3                         ABCK901249     10                    2

Here is my scripting to load the table into memory...

TRANSPORT_DATA_INT:

                    LOAD

Num 

(@1) as [Rev-Trac#],
          @2 as TX_Version,
          @3 as TX_Transport,
          @4 as TX_Sequence

FROM
[http://lpapp1/Data/corp/comm/fhit.nsf/0/440a3dbe3be9e20e852573fd0050b6c8/$FILE/QV-TransportData.TXT]
(txt, codepage is 1252, explicit labels, delimiter is '|', msq, header is 1 lines, filters(
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 5)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '---'))),
Remove(Row, RowCnd(CellValue, 1, StrCnd(equal              

, 'Request')))
));

I have attempted adding an addition load field of "Count(@1) as TX_COUNT and adding the group by statement after the loaction of the file, but have not had much luck.  Does anyone know what I need to do and what the proper script should be to get the Rev-Trac# counts?

[swuehl · Accepted solution]

Try adding this to your load script:

LEFT JOIN (TRANSPORT_DATA_INT)

LOAD     [Rev-Trac#],

              Count([TX_Version])      AS [TX_Count]

RESIDENT TRANSPORT_DATA_INT GROUP BY [Rev-Trac#];

[#3]

Worked like a charm!  Thanks for the help swuehl.

So that I understand... One would use the LEFT JOIN to ADD another column to an existing table.  Then when instructing the join where it is getting it's data from I have to use the RESIDENT call to call the already exisitng table.  Does that sound about right???

[swuehl]

Yes, I think that's correct.

Instead of the resident table, you can get the data from the original input source or a qvd, of course.