Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Show number of duplicates in front of each duplicate row

Hello!

I have a straight table with a 1st column with distinct values and a 2nd column with values (ID) that are sometimes repeated. How can I show, in a 3rd column, the number of different entries that have the same ID in front of each of those entries (as shown below)?

DateIDTotal number of lines with that ID
qX2
wX2
eY4
rZ1
tY4
yY4
uY4

This is a straight table that should change depending on the selection made on list boxes (i.e., depending on, for example, the week I select, the number of entries with a certain ID will change...)

Thanks in advance!

1 Solution

Accepted Solutions
MVP
MVP

Re: Show number of duplicates in front of each duplicate row

I assumed a straight table with two dimensions, ID and Date.

Then using my expression

=Count(TOTAL<ID> DISTINCT Date)


should not return the same number on each line.


SampleData:

LOAD * INLINE [

Date, ID

q, X

w, X

e, Y

r, Z

t, Y

y, Y

u, Y

];

Date ID Count(TOTAL<ID> DISTINCT Date)
7
eY4
qX2
rZ1
tY4
uY4
wX2
yY4
13 Replies

Re: Show number of duplicates in front of each duplicate row

May be add Count(ID) as the expression.

Not applicable

Re: Show number of duplicates in front of each duplicate row

that just puts a "1" in front of each row =/

Re: Show number of duplicates in front of each duplicate row

Is Date and ID coming from different tables?

Not applicable

Re: Show number of duplicates in front of each duplicate row

I have both in a Table Box that somebody else created (that gets the values from the company's internal system). I created a straight table because I need not only some values that are in the table box (such as Date and ID) but also some columns with calculated expressions.

I hope this helps, I'm new at QV...

Re: Show number of duplicates in front of each duplicate row

Can you press Ctrl + T to check if ID and Date are coming from same table in the backend or not

MVP
MVP

Re: Show number of duplicates in front of each duplicate row

Try something like

=Count(TOTAL<ID> DISTINCT Date)

Not applicable

Re: Show number of duplicates in front of each duplicate row

yes they are!

Not applicable

Re: Show number of duplicates in front of each duplicate row

that puts the total number of rows in front of each row. Considering the example above, it means there is a "7" in every row...

MVP
MVP

Re: Show number of duplicates in front of each duplicate row

I assumed a straight table with two dimensions, ID and Date.

Then using my expression

=Count(TOTAL<ID> DISTINCT Date)


should not return the same number on each line.


SampleData:

LOAD * INLINE [

Date, ID

q, X

w, X

e, Y

r, Z

t, Y

y, Y

u, Y

];

Date ID Count(TOTAL<ID> DISTINCT Date)
7
eY4
qX2
rZ1
tY4
uY4
wX2
yY4
Community Browser