Hi community i am new to matillion
trying to apply distinct on only some attributes using distinct component but that just drops all other remaining attributes
please help here
Hi @aarusajan — Welcome to the Matillion Community!
Only these selected columns are passed to the next component. Duplicate records are removed, leaving only distinct values. Completely guessing - what you may need to do is. join on the columns you select to be distinct back to the original data, using those columns as the join key(s).
Hope this helps,
Chika
If you are trying to use Matillion , you may use window functions (rank/row number) , partition on your distinct columns and filter the dataset using the window function. That will avoid the need for a self join.
Thank You for your help everyone, i will try it today
As @ChikaMatillionCommunityMgr mentioned distinct component functions like that.
Adding steps to follow to get all column along with distinct:
- In SQL Query (1): Add ROW ID to your data using - row_number() OVER( ORDER BY "Columns") as "ID"
- In SQL Query (2): On top of SQL Query (1) apply group by on columns required as unique and also select min("ID")
- In Join : SQL Query (1) Inner join SQL Query (1) on ID=min(ID)
You should get all columns with distinct/unique applied from join component, make sure to use correct columns in order by for creating ROW ID.
I Hope this helps.