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

    Count help

      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?

        • Re: Count help
          Stefan Wühl

          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#];

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