topic Remove Duplicates in Load in QlikView

Remove Duplicates in Load

khaycock — Sat, 16 Nov 2024 04:12:28 GMT

I have two fields that I need to make a key on and remove any duplicates of this key. How can I do this?

E.g:

Table1:

LOAD

Plugin,
[IP Address],
Plugin&[IP Address] AS KEY,
Field1,
Field2

FROM DATA

Re: Remove Duplicates in Load

Shubham_Deshmukh — Tue, 12 Mar 2019 10:58:40 GMT

WHat do you mean by remove duplicate?

Re: Remove Duplicates in Load

khaycock — Tue, 12 Mar 2019 11:01:05 GMT

There are duplicated versions of the KEY within the data. I want to remove that line of data.

Re: Remove Duplicates in Load

Shubham_Deshmukh — Tue, 12 Mar 2019 11:16:13 GMT

https://community.qlik.com/t5/New-to-QlikView/Remove-duplicates-in-Resident/td-p/973630

https://community.qlik.com/t5/QlikView-App-Development/removing-duplicate-records-on-load/td-p/808778

Re: Remove Duplicates in Load

khaycock — Tue, 12 Mar 2019 11:43:11 GMT

Tenable:
LOAD Plugin,
[IP Address],
[IP Address] & '_' & Plugin AS KEY1 ,
Replace([Patch Publication Date],'UTC','') as [Patch Publication Date],
(Replace([Last Observed],'UTC','')) as [Last Observed],
(Replace([First Discovered],'UTC','')) as [First Discovered]

FROM
[data.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Tenable_temp:
load distinct

*,
date(today(1)) - date(floor(date#([First Discovered],'MMM DD, YYYY hh:mm:ss')),'DD/MM/YYYY') as VulAgeNo,
if(date(today())-7 <= date(floor(date#([Last Observed],'MMM DD, YYYY hh:mm:ss'))), 'Scanned Recently', 'Re-Scan Needed') as ReScan
RESIDENT Tenable
WHERE NOT EXISTS (KEY1,[IP Address] & '_' & Plugin);

Drop table Tenable;

I think I followed the steps properly but this returns no results. Is it because I am forcing the key in the script?