Remove " duplicates "

[markthepig]

Hello community

Im loading from a catalog (*.xls) of products that looks like this

Cod          Description

10001     Bottled Pepsi 250m

10001     Bottled Pepsi 250ml blue

10001     Bottled Pepsi 250ml red

10002     Tea Green

10002     Tea Green 1 L

and so on...

I want to load the code but have only the first Description. In plain English eliminate the rest of descriptors for same Cod.

Is there any way to do this so at the end will have only ?

10001     Bottled Pepsi 250m

10002     Tea Green

and so on.

Thank you in advance.

Yours

Mark

[swuehl · Accepted solution]

LOAD Cod,

         Description

FROM YourExcel.xls

WHERE NOT EXISTS(Cod):

[kmswetha]

Hi,

You can flag rows that you wanted as Y and unwanted as N and filter it while loading.

[krishna_2644]

Hi Marketh,

To Achieve this ,you have to use 'First Sorted Value' function.

let me know if you have any questions.

Also see attached qvw for the code.

cheers.

[pratap6699]

WHERE NOT EXISTS(Cod)

eliminate all duplicated rows from table

10001     Bottled Pepsi 250m

10002     Tea Green

[krishna_2644]

Hi pratap,

he is not asking to eliminate the duplicates rather he wants the to pull only the 1st description.

you code eliminates duplicates but in the above examples one code has multiple descriptions which are not duplicates.

Thanks

krishna

[swuehl]

I think WHERE NOT EXISTS(Cod) should fulfill the requirement, unless field Cod has been loaded in a previous table already.

Then you need something like

LOAD Cod,

          Cod as CodCheck,

         Description

FROM YourExcel.xls

WHERE NOT EXISTS(CodCheck, Cod):

DROP FIELD CodCheck;

[sinanozdemir]

As far as I understand, you are trying to get the simplest description for each product. If so, then you need to use MinString, please see the below screenshot:

And your data model will look like the below:

[krishna_2644]

What if he has not loaded the field Cod in the previous table ?

[swuehl]

Then you can go with my very first suggested code:

product:

LOAD * INLINE [

Code,Desc

10001,Bottled Pepsi 250ml

10001,Bottled Pepsi 250ml blue

10001,Bottled Pepsi 250ml red

10002,Tea Green

10002,Tea Green 1 L

10002,Tea Green 2 L

]

WHERE NOT EXISTS(Code);