topic Re: Remove " duplicates " in QlikView

Remove " duplicates "

markthepig — Wed, 01 Jul 2015 14:41:56 GMT

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

Re: Remove " duplicates "

swuehl — Wed, 01 Jul 2015 14:57:31 GMT

LOAD Cod,

Description

FROM YourExcel.xls

WHERE NOT EXISTS(Cod):

Re: Remove " duplicates "

kmswetha — Wed, 01 Jul 2015 14:59:01 GMT

Hi,

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

Re: Remove " duplicates "

Qrishna — Wed, 01 Jul 2015 16:03:25 GMT

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.

Re: Remove " duplicates "

pratap6699 — Wed, 01 Jul 2015 16:57:24 GMT

WHERE NOT EXISTS(Cod)

eliminate all duplicated rows from table

10001 Bottled Pepsi 250m

10002 Tea Green

Re: Remove " duplicates "

Qrishna — Wed, 01 Jul 2015 17:09:09 GMT

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

Re: Remove " duplicates "

swuehl — Wed, 01 Jul 2015 17:13:53 GMT

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;

Re: Remove " duplicates "

sinanozdemir — Wed, 01 Jul 2015 17:15:45 GMT

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:

Re: Remove " duplicates "

Qrishna — Wed, 01 Jul 2015 17:18:45 GMT

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

Re: Remove " duplicates "

swuehl — Wed, 01 Jul 2015 17:52:18 GMT

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

Re: Remove " duplicates "

markthepig — Thu, 02 Jul 2015 07:51:10 GMT

Hello

Thank you very much for taking time to answer to my question. This is also a good solution for me, but not on this data source. I will use it on customers xls where aloso i have duplicates in names.Thank you again.

Later Edit:

This worked best in my case.

Thank you

Re: Remove " duplicates "

markthepig — Thu, 02 Jul 2015 07:59:33 GMT

Hello and thank you very much for the help you provided. I was not clear in my post. I needed whatever description not the first one. Also your solution will be remembered for future developments .Thank you again.