topic Re: load the record which has max occurrence in QlikView

load the record which has max occurrence

Anonymous — Tue, 30 Aug 2016 17:37:31 GMT

Hello

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

For example,

Name	Tag	Date
A	8880	5-30-2015
A	8880	5-29-2015
A	8880	5-14-2015
A	8880	6-12-2015
A	7769	6-1-2015
B	1113	6-5-2015
B	1113	6-7-2015
C	9998	6-1-2015
B	2220	8-17-2015
C	1234	6-15-2015
C	1234	2-10-2015
B	1113	9-16-2015
D	5430	10-9-2015
D	1124	8-20-2015

After load

Name	Tag
A	8880
B	1113
C	1234
D	5430

Date doesn't matter here.

thanks,

Josh

Re: load the record which has max occurrence

sunny_talwar — Tue, 30 Aug 2016 18:05:43 GMT

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;

Re: load the record which has max occurrence

rupamjyotidas — Tue, 30 Aug 2016 18:28:21 GMT

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;

Re: load the record which has max occurrence

effinty2112 — Tue, 30 Aug 2016 21:16:35 GMT

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
A	8880	4
B	1113	3
C	1234	2
D	1124	1
D	5430	1

There's a tie for top tag for Name D

Cheers

Andrew