Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ecabanas
Contributor

Deleted duplicated and Fill field

Hi all,

I have the following table:

ItemIdProveedor_TarifaTarifa_IdFromTarifaTo
10370808PROV0041710370808-4237001/01/20161,60
10370808PROV0041710370808-4246101/04/20161,60
10370808PROV0041710370808-4246101/04/20161,60
10370808PROV0041710370808-4249101/05/20162,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

1 Solution

Accepted Solutions
trdandamudi
Honored Contributor

Re: Deleted duplicated and Fill field

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-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".

5 Replies
Partner
Partner

Re: Deleted duplicated and Fill field

noconcatenate

table2:

load distinct

ItemIdProveedor_TarifaTarifa_IdFromTarifaTo

resident table;

drop table table;

Partner
Partner

Re: Deleted duplicated and Fill field

use distinct function in Load script  like below

Load distinct

A,

B

From table

trdandamudi
Honored Contributor

Re: Deleted duplicated and Fill field

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-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".

Highlighted
rrsrini2907
Contributor

Re: Deleted duplicated and Fill field

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-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.

ecabanas
Contributor

Re: Deleted duplicated and Fill field

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

Best

Eduard