topic Re: Remove Duplicate Values Using Script in QlikView

Remove Duplicate Values Using Script

oliveton — Thu, 23 Aug 2018 18:16:41 GMT

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

Re: Remove Duplicate Values Using Script

breno_morais — Thu, 23 Aug 2018 18:34:43 GMT

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.

Re: Remove Duplicate Values Using Script

vishsaggi — Thu, 23 Aug 2018 21:33:27 GMT

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];

Re: Remove Duplicate Values Using Script

oliveton — Fri, 24 Aug 2018 12:57:32 GMT

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

But it keeps saying field not found.

Re: Remove Duplicate Values Using Script

vishsaggi — Fri, 24 Aug 2018 14:52:40 GMT

Can you share the script where you using this partnumber?

Re: Remove Duplicate Values Using Script

oliveton — Fri, 24 Aug 2018 15:08:08 GMT

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......

Re: Remove Duplicate Values Using Script

vishsaggi — Fri, 24 Aug 2018 15:23:36 GMT

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;

Re: Remove Duplicate Values Using Script

BalaBhaskar_Qlik — Fri, 24 Aug 2018 16:48:01 GMT

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];

Re: Remove Duplicate Values Using Script

vishsaggi — Fri, 24 Aug 2018 16:54:53 GMT

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];

Re: Remove Duplicate Values Using Script

oliveton — Fri, 24 Aug 2018 17:01:40 GMT

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

Re: Remove Duplicate Values Using Script

oliveton — Fri, 24 Aug 2018 17:02:01 GMT

And I don't get how this one would remove my duplicates ?

Re: Remove Duplicate Values Using Script

Anonymous — Fri, 24 Aug 2018 17:07:06 GMT

Do you have more than one loading table? If you have, and it uses the same column name, the qlik will duplicate the line to not lose data!

If that is the case, post both scripts so then we can find a fix.

Good luck!

Re: Remove Duplicate Values Using Script

vishsaggi — Fri, 24 Aug 2018 17:07:07 GMT

Did you try may be with Firstsortedvalue? So can you share some sample data where you have this duplicates we can work on it.

Re: Remove Duplicate Values Using Script

vishsaggi — Fri, 24 Aug 2018 17:21:33 GMT

Can you try this:

JnpPies:

Load

Product as JnpPartTerm,

Text(B15)) as %Carm,

[ACES Applications],

[Part Terminology ID] as JnpPartTermID

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

(ooxml, embedded labels, table is Tabelle1)

;

JOIN

LOAD JnpPartTerm,

Sum(Cnt) AS RowCnt

Resident JnpPies

Group By JnpPartTerm;

Re: Remove Duplicate Values Using Script

oliveton — Fri, 24 Aug 2018 17:56:50 GMT

I do have more than 1 table loaded. I'm just attaching the actual qlik doc so you can see all the script.

I haven't tried firstsortedvalue.

An example of a duplicate value is JV8 under %Cram.

I tried the above join but it loaded with an error so I got rid of it.

Thank you all!

Re: Remove Duplicate Values Using Script

breno_morais — Fri, 24 Aug 2018 18:12:59 GMT

Something in your load script is wrong, you have %Cram as your primary key, but it has multiple loads.

Your column PartTearm have different values so the Qlik can't relate it, you need to fix your data

Is this the correct answer? If it's mark as CORRECT, it would help me a lot.

Re: Remove Duplicate Values Using Script

vishsaggi — Fri, 24 Aug 2018 19:58:21 GMT

Did you try this way?

JnpPies:

LOAD Distinct

// [Error message],

// PiesReceiver,

Product as JnpPartTerm,

Text(B15) as %Cram,

1 AS CramCount,

// [Hazardous Material Code],

// [Base Item ID],

// [Item Level GTIN],

// [GTIN Qualifier],

// [Brand AAIA ID],

// [Brand Label],

// [Sub Brand AAIAID],

// [SubBrand Label],

[ACES Applications] ,

// [Item Quantity Size],

// [Item Quantity Size UOM],

// [Container Type],

// [Quantity per Application Qualifier],

// [Quantity Per Application],

// UOM,

// [Effective Date],

// [Available Date],

// [Minimum Order Quantity],

// [Min Qty Order UOM],

// Group,

// [Sub Group],

// [AAIA Product Category Code],

// UNSPSC,

[Part Terminology ID] as JnpPartTermID

// [VMRS Code]

FROM

(ooxml, embedded labels, table is PiesItem);

JOIN(JnpPies)

LOAD JnpPartTerm,

Sum(CramCount) AS CramCnt

Resident JnpPies

Group By JnpPartTerm;

When you get an error can you screen shot the error and post it here please?