Need to count during load (noob question)

[toddbuss]

Hello.  I'm attempting to load a table of bed ID numbers that contains some (deliberate) duplicates.  I need a count of how many duplicates there are for each record.  I tried borrowing code from similar problems on the forum to no avail.  My failed attempt is below.  I know there is a simple solution for this.  Thanks.

[Bed Survey]:

LOAD

    @2 as [Facility Code],

    @3 as [Facility Name],

    @4 as [Bed Number],

    @2&' '&@4 as [Bed ID],

//concatenated Facility and Bed number to create unique Bed ID's.

FROM [lib://AttachedFiles/REPORTDD.XLS]

(txt, codepage is 1252, no labels, delimiter is '\t', msq, header is 1 lines);

[Cell Capacities]:

Load

   count([Bed ID])as [Room Capacity],

  [Bed ID]

Resident [Bed Survey]

[sunny_talwar · Accepted solution]

You need a group by clause here:

[Bed Survey]:

LOAD

    @2 as [Facility Code],

    @3 as [Facility Name],

    @4 as [Bed Number],

    @2&' '&@4 as [Bed ID],

//concatenated Facility and Bed number to create unique Bed ID's.

FROM [lib://AttachedFiles/REPORTDD.XLS]

(txt, codepage is 1252, no labels, delimiter is '\t', msq, header is 1 lines);

[Cell Capacities]:

LOAD Count([Bed ID])as [Room Capacity],

  [Bed ID]

Resident [Bed Survey]

Group By [Bed ID];

[toddbuss]

Thanks, Sunny.  It worked well.  Now I only need to figure out how it works.