topic Re: Remove Duplicates From a Load in QlikView

Remove Duplicates From a Load

kelsie_muller — Thu, 10 Dec 2020 21:19:56 GMT

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'

Re: Remove Duplicates From a Load

rubenmarin — Sat, 12 Dec 2020 12:20:23 GMT

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.

Re: Remove Duplicates From a Load

kelsie_muller — Mon, 14 Dec 2020 19:05:23 GMT

That did the trick! Thank you so much.

Re: Remove Duplicates From a Load

kelsie_muller — Fri, 12 Mar 2021 20:05:25 GMT

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?

Re: Remove Duplicates From a Load

rubenmarin — Sun, 14 Mar 2021 12:16:45 GMT

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/MappingFunctions/ApplyMap.htm

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