Loading & Counting Distinct records

Tue, 06 Aug 2013 21:31:54 GMT

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

Re: Loading & Counting Distinct records

nagaiank — Tue, 06 Aug 2013 22:06:52 GMT

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;

topic Re: Loading & Counting Distinct records in QlikView

Loading & Counting Distinct records

Re: Loading & Counting Distinct records