3 Replies Latest reply: May 21, 2013 5:16 PM by Stefan Wühl RSS

    Count help



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





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

      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?

        • Re: Count help
          Stefan Wühl

          Try adding this to your load script:



          LOAD     [Rev-Trac#],

                        Count([TX_Version])      AS [TX_Count]


            • Re: Count help

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