How to show duplicates?

[danielnevitt]

Hi,

I have the following script and I would like to write an expression in a straight table to show where there are duplicate records based on CO_CD, PRICE1 and CONTRACT_MONTHYEAR being the same (all of these fields the same, rather than duplicate records within each field).

// Start of [QlikviewPhysical.xml] LOAD statements

RECORD:
LOAD TRADE_TYPE,
TRADE_STATUS_IND,
STRATEGY_NUM,
TRADE_NUM,
DESK_CD,
OFFICE_CD,
PRICE1,
EXTENDED_AMT,
CO_CD,
BS_IND,
Date(Date#(CONTRACT_DT, 'MM/DD/YYYY')) AS CONTRACT_DT,
Week(Date#(CONTRACT_DT, 'MM/DD/YYYY')) AS CONTRACT_WEEK,
Month(Date#(CONTRACT_DT, 'MM/DD/YYYY')) AS CONTRACT_MONTH,
Year(Date#(CONTRACT_DT, 'MM/DD/YYYY')) AS CONTRACT_YEAR,
MonthName(Date#(CONTRACT_DT, 'MM/DD/YYYY')) AS CONTRACT_MONTHYEAR
FROM V:\ACL\ACL_Import\Qlikview\QlikviewPhysical.xml (XmlSimple, Table is [RECORDS/RECORD]);

// End of [QlikviewPhysical.xml] LOAD statements

ls this possible?

Any help would be much appreciated.

Regards,

Daniel

[#2]

Daniel

I would create this key in your script, i.e.

CO_CD & '-' & PRICE1 & '-' & CONTRACT_MONTHYEAR as %KEY;

Then in your straight table, use the formula Count(%KEY).  This will work as long as you have each of these fields as dimensions.

Let me know how you get on.

Steve

[danielnevitt]

Hi Steve,

Thank you very much for your reply.  That idea works perfectly and I have now created a straight table showing the count of %KEY.

Are you able to able the best way to restrict the table to only show where the %KEY count of distinct TRADE_NUM is greater than 2?

Thanks,

Daniel

[#4]

There's a couple of ways of doing this.  You could simply create an expression that only shows items above 2, such as;

=if(count(%KEY)<=2,0,count(%KEY))

Which will only ever return the duplicates.  Or you could create a Calculated Dimension, using the formula;

=if(count(%KEY)<=2, 'Not Duplicate', 'Duplicate')

This second option will give you more flexibility to filter on the Calculated Dimension, so you can see everything, just the Duplicates, or just the Non-Duplicates.

Can you mark my answer correct if I get it right?  And let me know how it goes.

Regards

Steve

[danielnevitt]

Hi Steve,

That first idea works great, thanks for your suggestion.

Are you able to advise how I can use that idea and only show DISTINCT TRADE_NUM?

Regards,

Daniel

[#6]

Should you only be getting one record for each TRADE_NUM?

If so just use the same formula for a calculated dimension;

=if(count(TRADE_NUM)<=2, 'Not Duplicate', 'Duplicate')

Again, TRADE_NUM will have to be a dimension for this to work.  Does this work for you?

[danielnevitt]

Hi Steve,

Each trade number can have multiple settlements against it.

Therefore I want to only show duplicates where the TRADE_NUM is distinct.

When I input the above as a calculated dimension, I receive //Error in calculation in my table.

Regards,

Daniel

[#8]

Daniel

Without doing any testing, I believe you can change the formula to also be dependent upon whether there is only one distinct TRADE_NUM, like so;

=if(count(%KEY)<=2 AND count(DISTINCT TRADE_NUM) = 1 , 0 , count(%KEY))

Alternatively, you could use the below;

=if( (count(%KEY)/Count(TRADE_NUM)) <= 2,0, (count(%KEY)/Count(TRADE_NUM)) )

Regards

Steve