Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
jchang_snwl
New Contributor II

load the record which has max occurrence

Hello

I try to create a scrip to load only the records which have the max occurrences.

For example,

NameTagDate
A88805-30-2015
A88805-29-2015
A88805-14-2015
A88806-12-2015
A77696-1-2015
B11136-5-2015
B11136-7-2015
C99986-1-2015
B22208-17-2015
C12346-15-2015
C12342-10-2015
B11139-16-2015
D543010-9-2015
D11248-20-2015

After load

NameTag
A8880
B1113
C1234
D5430

Date doesn't matter here.

thanks,

Josh

3 Replies
MVP
MVP

Re: load the record which has max occurrence

May be this:

Table:

LOAD Name,

    Tag,

    Date

FROM

[https://community.qlik.com/thread/230927]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table)

LOAD Name,

  Tag,

  Count(Tag) as Count

Resident Table

Group By Name, Tag;

FinalTable:

LOAD Name,

  FirstSortedValue(Tag, -(Count*100000+Date)) as Tag

Resident Table

Group By Name;

DROP Table Table;

rupamjyotidas
Valued Contributor

Re: load the record which has max occurrence

Maybe something like below. Again the script can be refined

Test:

LOAD Name,

     Tag,

     Date

FROM

(ooxml, embedded labels) ;

Test2:

Load

Name,

Tag,

Count(Tag) as CountTag

Resident Test Group by Name,Tag ;

Test3:

Load

Name,

Max(CountTag) as MaxCountTag

Resident Test2 Group by Name;

Left Join

Load

Name,

Tag,

CountTag as MaxCountTag

Resident Test2;

Drop Table Test,Test2;

effinty2112
Honored Contributor

Re: load the record which has max occurrence

Hi Josh,

You could try:

Data:

LOAD Name,

     Tag,

     Count(Tag) as CountTag

FROM

[https://community.qlik.com/thread/230927]

(html, codepage is 1252, embedded labels, table is @1)  Group by  Name,Tag;

MaxCount:

LOAD

Name,

Max(CountTag) as CountTag

Resident Data Group By Name;

Left Join(MaxCount)

LOAD * Resident Data;

drop table Data;

You get this:

Name Tag CountTag
A88804
B11133
C12342
D11241
D54301

There's a tie for top tag for Name D

Cheers

Andrew

Community Browser