topic Re: Remove duplicate fieldvalue in QlikView

Remove duplicate fieldvalue

Wed, 16 Sep 2015 14:29:54 GMT

Searched various posts regarding this issue but couldn't find any solution yet.

Due to an error in a 3rd party datadump I have various duplications in a sold volume field but they all have a different unique selling ID.

It is hard to repair the bug from 3rd party so I hope it can by solved by a loading script.

I like to keep one value of course and delete all others till next unique value appaers.

I attached the input source.

Looking forward for any reaction.

Regard,

Bert (rooky user)

Re: Remove duplicate fieldvalue

marcus_sommer — Wed, 16 Sep 2015 17:47:56 GMT

You could with peek() or previous() check values from former rows against the values from the current row and then set a flag which you could use to filter your data (per selection in gui or per where-clause within the script) - how it works: Pick() or Previous() ?

- Marcus

Re: Remove duplicate fieldvalue

sasiparupudi1 — Thu, 17 Sep 2015 08:27:42 GMT

use distinct

load distinct

field1,field2,field3

resident table;

hth

Sasi

Re: Remove duplicate fieldvalue

Kushal_Chawda — Thu, 17 Sep 2015 09:24:42 GMT

try,

Data:

load *

From Table;

New:

noconcatenate

load *,

if(rowno()=1,1, if( previous(Field1)=Field1 and previous(Field2)=Field2 and previous(Field3)=Field3,0,1)) as Flag

resident Data

order by ID; // Here you have to do order by with key

Drop table Data;

Here, I have taken random 3 fields but in your case you will have all the Fields of that table. So you will have if condition for all the fields of the table

Final:

noconcatenate

load *

resident Data

where Flag =1;

drop table New;