Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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