Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading & Counting Distinct records

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

1 Reply
nagaiank
Specialist III
Specialist III

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;