Discussion board where members can get started with QlikView.
I wish to have a column that show the no. of repeated record :
I tried various COUNT & SUM filtering but failed. Appreciate if anyone could help.
Original table :
Expected result :
OMSPO# trackCode StatusDate(First) Count
00O30868 REVPO 02/03/2015 2
00O55871 REVPO 16/01/2015 3
00O35872 REVPO 16/01/2015 1
See if the attachment serves you.
View solution in original post
if you have OSMSPO# and trackCode as dimensions and then FirstSortedValue(StatusDate) and Count(StatusDate) as your two measures that should get you to that answer.
...sorry ... firstsortedvalue won't work in this context because you need something to sort by.
max(StatusDate) or min(StatusDate) would give you the earliest or latest date against each OMSOP#.
If you truly want just the first date then you would have to add a field to your load with "RowNo() as RowNumber" and then use firstsortedvalue(StatusDate,RowNumber)