Qlikview - count repeated data

[Anonymous]

Hi all,

I need to count repeated data from my excel file using qlikview. I have next table

Format od date (DD-MM-YY)

I need to count Item that repeated in table but item that is repeated day after day in a row.

When i use

count(Item) 

i got next table

but I don't wont this i want table like this:

because Item I1 repeat in date 01-05-17 and then in date 02-05-17, i don't need item I2 because don't repeat day after day.

I try also

=avg( aggr( count([Item) ,Date) )

Any idea???

[MindaugasBacius]

Could you please provide excel file with your example?

[Anonymous]

I add file

[Anil_Babu_Samineni]

As per your data, You have only one Item for that specific date which is repeated in Every month start date. So that you may output required I1 -- 1. If not, You can explain more

[Anil_Babu_Samineni]

Try this? And check whether how you are getting

Aggr(Count({<Date = {'01/05/2017'}>}Item),Date)

[Anonymous]

I try and returns in date 01/05/2017 duplicate value, not I1 value 2

[Anil_Babu_Samineni]

I used the same, And it's working as expected

Change the Environment in Qlikview like

SET DateFormat='DD/MM/YYYY';

Then Use below one

Aggr(Count({<Date = {'$(=Min(Date))'}>}Item),Date)

[MindaugasBacius · Accepted solution]

I am not sure if that's the most effective way to do this:

tmp:

LOAD Location,

     Item,

     Result,

     Date

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

tmp1:

LOAD *

Resident tmp

Order By Item asc, Date desc;

DROP Table tmp;

NoConcatenate

tmp:

LOAD *

  ,If(Item = Peek(Item) and Peek(Date) - 1 = Date, 1, 0) as Flag_tmp

Resident tmp1;

DROP Table tmp1;

NoConcatenate

tmp1:

LOAD *

Resident tmp

Order By Item asc, Date asc;

DROP Table tmp;

NoConcatenate

tmp:

LOAD Location,

     Item,

     Result,

     Date

  ,If(Item = Peek(Item) and Peek(Date) + 1 = Date, 1, 0) + Flag_tmp as Flag

Resident tmp1;

DROP Table tmp1;

Result:

[Anonymous]

Thanks a lot!!!!

[Anonymous]

But if i have three date one from another like 01-05-2017,02-05-2017,03-05-2017 from Item I1 I got result 4 instead 3???