Remove Duplicate Values Using Script

[oliveton]

Hello,

In one of my excel files I loaded, there are duplicate part numbers.

I want to know how to remove the duplicates in the script.

The duplicates are coming from the "BuyersGuide" file shown below.

Thanks

[breno_morais]

Hello,

You can use "Where Not" with "Previous" for check if have other.

Like this:

Table1:

Load

[Product Line] as ID,

[ANY] as Subject

Resident Table

Where Not [Product Line] = Previous([Product Line])

Order By [Product Line], [ANY];

I help you? Mark correct and like.

[vishsaggi]

Breno may be this hes looking at Partnumber duplicates. So you can change Product Line with Partnumber.

Table1:

Load

[Product Line] as ID,

[ANY] as Subject

Resident Table

Where Not Partnumber = Previous(Partnumber)

Order By [Product Line], [ANY];

[oliveton]

Hi, yes. I had to change it to PartNumber.

But it keeps saying field not found.

[vishsaggi]

Can you share the script where you using this partnumber?

[oliveton]

I actually discovered the duplicates were in a different area of the script than previously so here it is below.

If the duplicates could be removed from %Cram......

[vishsaggi]

Just try like in preceding load use Breno's where condition like below:

LOAD *

WHERE NOT %Cram = Previous(%Cram);

LOAD DISTINCT

Product AS JnpPartterm,

ACES Applications,

.....

....

FROM YOurpath;

[balabhaskarqlik]

May be like this:

JnpPies:

Load

    Product as JnpPartTerm,

    Firstsortedvalue(Text(B15)) as %Carm,

    [ACES Applications],

    [Part Terminology ID] as JnpPartTermID

    Max([Part Terminology ID]) as MaxJnp

From [lib://DATA/mehrere-spalten-vergleichen.xlsx] 

      (ooxml, embedded labels, table is Tabelle1) 

    Group BY

    Product, [AES Applications];

[vishsaggi]

HI Bala,

May be this will throw an error, coz there are fields which are not in Group By. IF there are any non aggr fields in LOAD list it will throw an error. May be this:

JnpPies:

Load

    Product as JnpPartTerm,

    Firstsortedvalue(Text(B15)) as %Carm,

    [ACES Applications],

    [Part Terminology ID] as JnpPartTermID

    Max([Part Terminology ID]) as MaxJnp

From [lib://DATA/mehrere-spalten-vergleichen.xlsx]

      (ooxml, embedded labels, table is Tabelle1)

    Group BY

    Product, [AES Applications], [Part Terminology ID];

[oliveton]

I tried this but the duplicates are still there for some reason.