Removing duplicate rows

[ali_hijazi]

Hello

In sql I can give each row by customer and I'd or sequence number using row number along with partition as per below:

This way I can keep the rows where rn = 1

Is there a way similar to this in Qlik while I'm reading data from a qvd file?

I thought of sorting data by customer an date desc then using previous(customer) I can identify the first row of each customer and thus add a flag to that row as 1

Then. I filter in the rows where this flag is 1

Kindly advise

 

[Koustav]

Hey , 

The Simple answer is you can just use RowNo() function to get the Row Number .

For your requirement :

1. Load your Qvd file as a whole :

           A:

           LOAD * FROM "XYZ.QVD";

2. LOAD the data using Resident of previous table and use Order by Order_Date Desc.

     NoConcatenate

     B:

    LOAD customer_id,

               order_id,

               RowNo() as rn

            Resident A  Order by Order_Date Desc;

            Drop Table A;

3. Then you can just add preceding load or one more resident load to get your data:

   NoConcatenate

   C:

   Load *

   Resident B

   where rn = 1;

   Drop Table B

;

[QFabian]

Hi @ali_hijazi  Please check Qlik Help :

https://help.qlik.com/en-US/sense/November2024/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFun...

[Qrishna]

Use RecNo() as SNo, Main Difference between recNo() and Rown() is Recno() acts while the  Loading the Table while Rowno() acts on already Loaded table.

[Kushal_Chawda]

@ali_hijazi  try below

Data:
LOAD *
FROM table;

T1:
LOAD *,
if(customer_id<>previous(customer_id),1,rangesum(peek('rn'),1)) as rn
resident Data
order by customer_id,order_date desc;

drop table Data;

Final:
noconcatenate
LOAD *
resident T1
where rn=1;

drop table T1;

[Kushal_Chawda]

another option:

Data:
LOAD customer_id,
     order_id,
     order_date
FROM table;

inner join(Data)
LOAD customer_id,
     max(order_date) as order_date
resident data
group by customer_id;