topic Re: Need to count during load (noob question) in QlikView

Need to count during load (noob question)

toddbuss — Wed, 25 Nov 2020 16:16:04 GMT

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]

Re: Need to count during load (noob question)

sunny_talwar — Tue, 09 May 2017 18:39:54 GMT

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

Re: Need to count during load (noob question)

toddbuss — Tue, 09 May 2017 18:44:18 GMT

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