Trying to group and count by a firstsortedvalue column

[craz123]

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)

[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;

[tresesco]

POW, shouldn't it be 1?

[craz123]

Sorry yes it is 1

[craz123]

Is there an expression solution form for this? 

[tresesco · Accepted solution]

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

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

[craz123]

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