Urgent question seeking help

How to detect DUPLICATED records and output them?

How to find if a variable is fully $null$? (recognized as typeless so can't use Data Audit node)

hunterdong:

How to detect DUPLICATED records and output them?

Distinct node, option "Discard"

hunterdong:

How to find if a variable is fully $null$? (recognized as typeless so can't use Data Audit node)

Here you have to test if the field is Null or Blank or undefined

selectnode, condition : (@NULL(@FIELD) or @BLANK(@FIELD) or @FIELD= undef)

 

You'll find all information about this kind of function in the clementine help 

thank you very much. That day I tried distinction node, and it worked but I did the wrong calculation ( Distinction Node produced 500 results which is correct but I thought 500>  (380+265) from my append node so I wandered why it produced more result.
 

There is an easy way to identify duplicate rows and perform ranking to get the highest or most recent (or lowest / oldest).  If you use the aggregate node to summarise rows by all your key fields (whatever fields you are looking for duplicates in) then any outputs that have a 'row count' above 1 are duplicated. 

So, simply use an aggregate node, followed by a select to get only rows that have a row count above 1.  This will return all rows that are duplicates.

If the rows are not completely duplicated, and some fields have higher values or dates, then you can use aggregate with max to identify the most recent row of any duplicates.  Attached zip file contains a stream and data to illustrate this concept.

This method will be faster to process, especially against an indexed data warehouse.

Cheers

Tim