topic Re: Remove duplicate field in QlikView

Remove duplicate field

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

I have created a compound key of 2 fields that I want to only see data from in my straight table. However, this key is duplicated on some rows, how can I get it to only display one of these duplicates? The only reason it is duplicated is because of a 'last scanned' field that I can ignore but I do need to display it within the table, so can't remove it

I'm not sure how to do this in the load when I want all the rest of the data within that load to be loaded normally and not distinct?

Re: Remove duplicate field

vishsaggi — Mon, 08 Oct 2018 16:07:37 GMT

Can you share some sample file with an example please?

Re: Remove duplicate field

khaycock — Tue, 09 Oct 2018 08:19:43 GMT

Sure. For lines where I have duplicate Plug & Code, I want to only display the most recent Date2 line. Does that make sense?

Re: Remove duplicate field

Frank_Hartmann — Tue, 09 Oct 2018 08:39:31 GMT

try this:

Directory;
LOAD [1] as Plug, 
     [2] as Code, 
     [3] as Date1, 
     if(previous([1])= [1] and previous([2])=[2] and previous([3])=[3],previous([4]),[4]) as Date2
FROM

(biff, embedded labels, table is Sheet1$);

Re: Remove duplicate field

Frank_Hartmann — Tue, 09 Oct 2018 08:45:59 GMT

and if only want to keep the most recent date2 than try this:

LOAD Plug, 
     Code, 
     Date1,
     Date2, 
      if(previous(Plug)= Plug and previous(Code)=Code and Previous(Date1)=Date1,Date2,previous(Date2)) as Date3
FROM

(biff, embedded labels, table is Sheet1$);

Re: Remove duplicate field

khaycock — Tue, 09 Oct 2018 09:47:42 GMT

When I use this field in the table it still displays both dates

When I only want it to display the data for the most recent 'DistinctDate'

Re: Remove duplicate field

Frank_Hartmann — Tue, 09 Oct 2018 10:12:46 GMT

give this a try:

Directory;
LOAD [1] as Plug,
     [2] as Code,
     [3] as Date1,
     [4] as Date2  
FROM

(biff, embedded labels, table is Sheet1$)
where if(previous([1])= [1] and previous([2])=[2] and previous([3])=[3],[4],Null())<>Null();