topic Re: Deleted duplicated and Fill field in QlikView

Deleted duplicated and Fill field

ecabanas — Mon, 09 May 2016 10:14:20 GMT

Hi all,

I have the following table:

ItemId	Proveedor_Tarifa	Tarifa_Id	From	Tarifa	To
10370808	PROV00417	10370808-42370	01/01/2016	1,60
10370808	PROV00417	10370808-42461	01/04/2016	1,60
10370808	PROV00417	10370808-42461	01/04/2016	1,60
10370808	PROV00417	10370808-42491	01/05/2016	2,00	-

As you can see there are two duplicated lines, and I want to delete one of them. Is it possible to do it in load script?

Many thank's

Eduard

Re: Deleted duplicated and Fill field

florentina_doga — Mon, 09 May 2016 10:17:00 GMT

noconcatenate

table2:

load distinct

ItemId

Proveedor_Tarifa

Tarifa_Id

From

Tarifa

resident table;

drop table table;

Re: Deleted duplicated and Fill field

perumal_41 — Mon, 09 May 2016 10:21:17 GMT

use distinct function in Load script like below

Load distinct

From table

Re: Deleted duplicated and Fill field

trdandamudi — Mon, 09 May 2016 10:22:22 GMT

May be as below:

Temp_Table:

Load * Inline [

ItemId, Proveedor_Tarifa, Tarifa_Id, From, Tarifa, To

10370808, PROV00417, 10370808-42370, 01/01/2016, 1,60

10370808, PROV00417, 10370808-42461, 01/04/2016, 1,60

10370808, PROV00417, 10370808-42491, 01/05/2016, 2,00 -

];

NoConcatenate

Final:

Load *

Resident Temp_Table

Where [Tarifa_Id] <> Previous([Tarifa_Id])

Order By Tarifa_Id;

Drop Table Temp_Table;

Note: Duplicates are removed based on the field "Tarifa_Id".

Re: Deleted duplicated and Fill field

rrsrini2907 — Mon, 09 May 2016 10:25:47 GMT

Hi,

As mentioned above, the distinct will simplify.

But if there are columns which make unique, then you can follow the below script.

Here the %Key indicate the fields through which you want to identify the unique records and I also create index.

Finally applying inner join to the data set will create unique records.

Data:

LOAD *,

ItemId &'^'& Proveedor_Tarifa &'^'& Tarifa_Id as %Key,

RowNo() as Index;

LOAD * Inline [

ItemId,Proveedor_Tarifa,Tarifa_Id,From,Tarifa,To

10370808,PROV00417,10370808-42370,01/01/2016,1,60

10370808,PROV00417,10370808-42461,01/04/2016,1,60

10370808,PROV00417,10370808-42491,01/05/2016,2,00

];

TempFirstRecord:

LOAD %Key,

Min(Index) as MinRecord

Resident Data

Group by %Key;

Inner Join (Data)

LOAD %Key,

MinRecord as Index

Resident TempFirstRecord;

Regards,

Srini.

Re: Deleted duplicated and Fill field

ecabanas — Mon, 09 May 2016 10:30:42 GMT

Many thank's Thirumala Dandamudi that's the way, with distinc did not work, with previous works great!!! many many thanks!!

Best

Eduard