topic Re: Eliminate duplicates in a QV script in QlikView

Eliminate duplicates in a QV script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a file with duplicates, the way to detect a dup is tha the code has a D at the final

100

100D

101

102

103

103D

104

105

I want to eliminate dups and I know the right one is the one with D al the ened.

In this case, the file with not dups will be

100D

101

102

103

104

105

I have made a script, but think I do too many steps, can do this in a easier way, with less steps.

My file has 10 million rows...

Contratos:

LOAD text(@1) as contrato1, left(@1,3) as contrato_aux

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

NoConcatenate

Contratos2:

LOAD contrato_aux, contrato1 Resident Contratos Order by contrato1 DESC ;

NoConcatenate

Contratos3:

LOAD contrato1, contrato_aux, contrato_aux as contrato_aux1

Resident Contratos2 WHERE NOT Exists (contrato_aux1, contrato_aux);

DROP Table Contratos;

DROP Table Contratos2;

Re: Eliminate duplicates in a QV script

Chanty4u — Tue, 12 Apr 2016 12:24:08 GMT

use

PurgeChar(s1, s2)

Returns the string s1 less all characters contained in string s2.

Example:

purgechar ( 'q1li2kview3','123' ) returns 'qlikview'

Re: Eliminate duplicates in a QV script

swuehl — Tue, 12 Apr 2016 12:24:29 GMT

What happened to 103D?

Re: Eliminate duplicates in a QV script

Chanty4u — Tue, 12 Apr 2016 12:26:09 GMT

Purgechar is a QlikView Function not a Foxpro one. You should use it in the LOAD part of your statement not in the SQL SELECT.

LOAD Purgechar(Field,'D') AS Field

SQL SELECT Field

FROM TABLE;

Using purgechar after the SQL sentence will cause an error.

Re: Eliminate duplicates in a QV script

ariel_klien — Tue, 12 Apr 2016 12:27:32 GMT

Hi,

load *

from....

where right(contrato1,1)<>'D'

Ariel

Re: Eliminate duplicates in a QV script

Anonymous — Tue, 12 Apr 2016 13:11:01 GMT

I am sorry....103 is the right one

The output needed

100D

101

102

103D

104

105

Re: Eliminate duplicates in a QV script

Anonymous — Tue, 12 Apr 2016 13:14:20 GMT

My problem is my field is unique, y key is Field1 and when is repated appear:

field

fieldD

when is no repeated appear only field (not other row with fieldD)

if I have two rows

100

100D

I need 100D in the output

If I have

102

I need 102 in the output

I need the one with D at the end if the code is repeated

Thanks

Re: Eliminate duplicates in a QV script

Anonymous — Tue, 12 Apr 2016 13:15:07 GMT

This is not the problem...., thanks

Re: Eliminate duplicates in a QV script

Anonymous — Tue, 12 Apr 2016 13:15:45 GMT

I don't want to purge the 'D' this is not the problme