How to find Dups

[#1]

Hi everyone,

I have a weird situation here where I am trying to find dup records. Please look at the table below.

Style	National Color Code	Internal Color Code
111	AA	aa
111	AA	bb
111	BB	dd
111	CC	cc
111	DD	dd
222	AA	bb
222	BB	aa
222	AA	aa
333	GG	gg
333	HH	hh

I am trying to find where the style and national color code are the same but more than 1 different internal color code. In this table above we should have 2 dups which are Style '111' and National Color Code 'AA' and Style '222' and National Color Code 'AA'.

Thank you in advance.

[sunny_talwar · Accepted solution]

May be this?

Table:

LOAD * INLINE [

    Style, National Color Code, Internal Color Code

    111, AA, aa,

    111, AA, bb,

    111, BB, dd,

    111, CC, cc,

    111, DD, dd,

    222, AA, bb,

    222, BB, aa,

    222, AA, aa,

    333, GG, gg,

    333, HH, hh

];

Left Join (Table)

LOAD Style,

  [National Color Code],

  If(Count(DISTINCT [Internal Color Code]) > 1, 1, 0) as DupsFlag

Resident Table

Group By Style, [National Color Code];