Remove Duplicates From a Load

[kelsie_muller]

Hello,

I have a load script that returning duplicate values. I am hoping to return only unique values.

Please see the load script below: "Resource.Name" is the unique identifier of an asset, the "Routing Date" value indicates the last time the asset was updated. Some of the assets have been routed multiple times, thus returning multiple values in the table visualization.  I would only like to show ONE unique instance (doesn't matter which) of the "Resource.Name" value in my load script.  

select
c.name 'Resource.Name',
a.pool_name 'Workflow Exited',
a.exit_type 'Routing Value',
a.date 'Routing Date',
u.last_name 'Last Name',
cd1.value as 'Supplier.OriginalName',
cd2.value as 'AETN.PacID',

from
inv.clips c
join tem.workflow_audit a on a.clip_id = c.id
join tem.users u on a.user_id = u.id
join inv.suppliers s on c.supplier_id = s.id
left join inv.clip_data cd1 on cd1.clip_id = c.id and cd1.name = 'Supplier.OriginalName'
left join inv.clip_data cd2 on cd2.clip_id = c.id and cd2.name = 'AETN.PacID'

[rubenmarin · Accepted solution]

Hi @kelsie_muller, you can use Exists() like:

LOAD * Where not Exists(Resource.Name);
SELECT...

This way when Reosurce.Name has been already loaded it will not be loaded again.

[kelsie_muller]

That did the trick! Thank you so much.

[kelsie_muller]

Hi Ruben - Thank you again for the assist back in December!

The requirements of my load script have now changed. I still need unique values, but now I need to identify the most recent update to an asset. Is it possible to achieve this with an alteration to the load script?

[rubenmarin]

Hi Kelsie, you can bring the update date to the main table using a Join or a mapping table, then you can use the same exists(Asset) funtion but adding an 'order by [dateField] desc' clause. This way you will load the most recent updated value of each Asset

https://help.qlik.com/es-ES/sense/November2020/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFun...

https://help.qlik.com/es-ES/sense/November2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...