Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ecabanas
Not applicable

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
Not applicable

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
florentina_doga
Not applicable

Re: Deleted duplicated and Fill field

noconcatenate

table2:

load distinct

ItemIdProveedor_TarifaTarifa_IdFromTarifaTo

resident table;

drop table table;

perumal_41
Not applicable

Re: Deleted duplicated and Fill field

use distinct function in Load script  like below

Load distinct

A,

B

From table

trdandamudi
Not applicable

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
Not applicable

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
Not applicable

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