topic Removing duplicate rows in App Development

Removing duplicate rows

ali_hijazi — Sat, 21 Dec 2024 14:34:09 GMT

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

Re: Removing duplicate rows

Koustav — Sat, 21 Dec 2024 15:39:20 GMT

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 :

LOAD * FROM "XYZ.QVD";

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

NoConcatenate

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

Load *

Resident B

where rn = 1;

Drop Table B

;

Re: Removing duplicate rows

QFabian — Sat, 21 Dec 2024 15:42:22 GMT

Hi @ali_hijazi Please check Qlik Help :

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

Re: Removing duplicate rows

Qrishna — Sat, 21 Dec 2024 21:39:37 GMT

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.

Re: Removing duplicate rows

Kushal_Chawda — Mon, 23 Dec 2024 11:15:34 GMT

@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;

Re: Removing duplicate rows

Kushal_Chawda — Mon, 23 Dec 2024 11:15:07 GMT

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;