topic Re: Qlikview - count repeated data in QlikView

Qlikview - count repeated data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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???

Re: Qlikview - count repeated data

MindaugasBacius — Fri, 05 May 2017 11:28:37 GMT

Could you please provide excel file with your example?

Re: Qlikview - count repeated data

Anonymous — Fri, 05 May 2017 11:31:12 GMT

I add file

Re: Qlikview - count repeated data

Anil_Babu_Samineni — Fri, 05 May 2017 11:48:58 GMT

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

Re: Qlikview - count repeated data

Anil_Babu_Samineni — Fri, 05 May 2017 11:52:13 GMT

Try this? And check whether how you are getting

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

Re: Qlikview - count repeated data

Anonymous — Fri, 05 May 2017 11:55:40 GMT

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

Re: Qlikview - count repeated data

Anil_Babu_Samineni — Fri, 05 May 2017 11:58:20 GMT

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)

Re: Qlikview - count repeated data

MindaugasBacius — Fri, 05 May 2017 11:58:31 GMT

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:

Re: Qlikview - count repeated data

Anonymous — Fri, 05 May 2017 12:06:37 GMT

Thanks a lot!!!!

Re: Qlikview - count repeated data

Anonymous — Fri, 05 May 2017 12:52:39 GMT

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???

Re: Qlikview - count repeated data

MindaugasBacius — Fri, 05 May 2017 13:29:15 GMT

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, If(Flag_tmp = 0, 1, 0), 0) + Flag_tmp as Flag

Resident tmp1;

DROP Table tmp1;

Re: Qlikview - count repeated data

Anonymous — Fri, 05 May 2017 15:52:39 GMT

it works, thanks