Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Can you share some sample file with an example please?
Sure. For lines where I have duplicate Plug & Code, I want to only display the most recent Date2 line. Does that make sense?
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$);
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$);
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'
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();