Loading & Counting Distinct records

[#1]

Hi guys,

Hope somebody can help. I have a data file consisting of reference numbers to load into Qlikview, i know there are duplicates in the list, so only want each record once, but I also want to create a second field counting the duplicates

ie. source file

Ref_number

12345

47254

18645

76538

12345

18645

12345

and I want the resulting file to look as follows

Ref_number     Ref_count

12345               3

47254               1

18645               2

76538               1

hope this makes sense.

I assume I would need some form of LOAD Distinct and count functions but can't get it to work.

Any pointers appreciated

Andrew

[nagaiank]

One way of doing this is using the following script:

Temp:

LOAD *, 1 as No;

LOAD * Inline [

Ref_number

12345

47254

18645

76538

12345

18645

12345

];

Data:

NoConcatenate LOAD Ref_number, Sum(No) as Count Resident Temp Group By           Ref_number;

DROP Table Temp;