topic Re: How do I suppress duplicates in a straight table? in QlikView

How do I suppress duplicates in a straight table?

Tue, 06 Dec 2016 14:48:57 GMT

I can't use a pivot table for this, but I need to make sure that in the ITMSNumber column we are not showing any duplicates. PSA

Re: How do I suppress duplicates in a straight table?

vinieme12 — Tue, 06 Dec 2016 14:52:52 GMT

Don't load the duplicates in your data, why do you have duplicate rows anyway?

Re: How do I suppress duplicates in a straight table?

Tue, 06 Dec 2016 14:54:54 GMT

I'm asking the question so I don't have duplicate rows. I work in a large company so I don't know why there is duplicates. How would I go about writing an expression or a script to prevent loading duplicates?

Re: How do I suppress duplicates in a straight table?

vinieme12 — Tue, 06 Dec 2016 14:58:48 GMT

Well the Question you asked was to hide the duplicate rows!

Anyways, firstly you need to identify what is creating those duplicates,

Are you doing a concatenation operation during data load, then add a flag to identify the source for your data for each record

Re: How do I suppress duplicates in a straight table?

vinieme12 — Tue, 06 Dec 2016 15:00:13 GMT

if you don't want to do that exercise then just do

AGGR(concat(Distinct Fieldname),ITSMNumber) , in Calculated Dimension

Re: How do I suppress duplicates in a straight table?

krishnacbe — Tue, 06 Dec 2016 15:03:21 GMT

Hi,

Does the straight table has only two dimensions ITSM & Name?

If not then the other dimensions are causing the issue. If Yes your expression should filter the duplicate rows.

Re: How do I suppress duplicates in a straight table?

Clever_Anjos — Tue, 06 Dec 2016 15:07:49 GMT

Please, try formatting ITMSNumber with more precision, maybe it has significant values after "."

Re: How do I suppress duplicates in a straight table?

Tue, 06 Dec 2016 15:11:28 GMT

It has dimensions ITMSNumber, Name, AM Contact, Data Steward, Pr Yr, % Complete, Incomplete Fields, Data Ops Notes, IT Notes

Re: How do I suppress duplicates in a straight table?

vinieme12 — Tue, 06 Dec 2016 15:19:20 GMT

this will work

AGGR(concat(Distinct Fieldname),ITSMNumber)

if your record has more distinguishing factors to identify a unique record then kep them adding to the AGRR()

else i would suggest using a key field that identifies unique rows Which i think should be ITSMNumber.

Re: How do I suppress duplicates in a straight table?

krishnacbe — Tue, 06 Dec 2016 15:21:34 GMT

Can you check only ITMS number & Name columns only have duplicate or entire row itself is duplicated.

I hope due to the Dimensions you have added it is showing distinct values. that is the representation of Straight table. If you don't want to view like duplicates then you need to convert the straight table to Pivot.

Re: How do I suppress duplicates in a straight table?

Tue, 06 Dec 2016 15:59:07 GMT

I tried doing your code in calculated dimension in the straight table where FieldName is ITMSNumber and it didn't work.

Re: How do I suppress duplicates in a straight table?

Tue, 06 Dec 2016 17:40:57 GMT

I don't know what you mean...

Re: How do I suppress duplicates in a straight table?

Tue, 06 Dec 2016 17:41:31 GMT

Vineeth, can you please provide a step by step guide on how to do this?

Re: How do I suppress duplicates in a straight table?

vinieme12 — Tue, 06 Dec 2016 20:17:58 GMT

See Attached app, made my own sample

TEMP:

LOAD *,rowno() as KEY INLINE [

ITMSNumber,Comment,detail

1,aaaaaa,a1

1,aaaaaa,a2

2,bbbbbbb,b1

2,bbbbbbb,b2

3,ccccccc,c1

3,ccccccc,c2

];

Chart Dimension:

ITMSNumber

=aggr(Concat(DISTINCT detail),ITMSNumber,KEY)

=aggr(Concat(DISTINCT Comment),ITMSNumber,KEY)

if you do not have any expressions uncheck Supress Missing & uncheck Zero Values from Presentation tab.

Re: How do I suppress duplicates in a straight table?

Anonymous — Wed, 07 Dec 2016 10:15:56 GMT

Hi,

The duplication happens because some where in your data model, One value of "ITMS number" or "Name" is mapped to different value in the rest columns, which you should expect 1:1 map.

As you also have a lot other columns, can you double check and confirm that the rest columns in the duplication columns is the same?

Zhihong