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: 
craz123
Contributor
Contributor

Trying to group and count by a firstsortedvalue column

Hi all,

I want to get the count of all visitIds and group them by names that are last in the seqeunce of each visitId

I have this data

visitId | name | sequence

V1, ABC, 1
V1, XYZ, 2

V2, ABC, 1
V2, XYZ, 2

V3, ABC, 1
V3, XYZ, 2
V3, POW, 3

I have got it in this form:

visitId | FirstSortedValue([name], -[sequence])

V1, XYZ
V2, XYZ
V3, POW

I want it in this form:
XYZ | 2
POW | 1

Could you advice please, an expression based solution (not load script)

Labels (4)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

tresesco_0-1677574987291.png

Dim : Aggr(FirstSortedValue([name], -[sequence]), visitId)

Exp : Count(Aggr(FirstSortedValue([name], -[sequence]), visitId))

 

View solution in original post

6 Replies
MayilVahanan

Hi

Try like below

T:
Load * Inline
[
visitId , name , sequence
V1, ABC, 1
V1, XYZ, 2
V2, ABC, 1
V2, XYZ, 2
V3, ABC, 1
V3, XYZ, 2
V3, POW, 3

];

Final:

Load Distinct Name, Seq;
Load visitId, FirstSortedValue(name, -sequence) as Name, FirstSortedValue(sequence, -sequence) as Seq Resident T Group by visitId;

Drop Table T;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
tresesco
MVP
MVP

POW, shouldn't it be 1?

craz123
Contributor
Contributor
Author

Sorry yes it is 1

craz123
Contributor
Contributor
Author

Is there an expression solution form for this? 

tresesco
MVP
MVP

tresesco_0-1677574987291.png

Dim : Aggr(FirstSortedValue([name], -[sequence]), visitId)

Exp : Count(Aggr(FirstSortedValue([name], -[sequence]), visitId))

 

craz123
Contributor
Contributor
Author

This is perfect! Thank you so much. If you could just explain how the dimension works, would be superb!! (Still confused how it works)