topic Count repeated record in QlikView

Count repeated record

Thu, 28 Jan 2016 08:00:48 GMT

Hi,

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.

Thank you.

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

Re: Count repeated record

dominicmander — Thu, 28 Jan 2016 18:07:26 GMT

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.

Re: Count repeated record

dominicmander — Thu, 28 Jan 2016 18:17:06 GMT

...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)

Re: Count repeated record

Anonymous — Thu, 28 Jan 2016 18:26:02 GMT

See if the attachment serves you.