Load Distinct not working as expected

[JustinDallas]

Hello Folks,

I have the following script where I am working to dedupe some dates and their respective tractorId.  However, my table that should have unique values isn't working.

When my script executes, I'm left with this:

Below is my script, and I'm mnot sure why the DISTINCT is failing to catch the rows.

ActivityDates:
LOAD %tractor ,
Date(TimeStamp(TimeStamp#(distinct_lgh_enddate,'M/D/YYYY h:mm'))) AS distinct_lgh_enddate,
Date(TimeStamp(TimeStamp#(distinct_lgh_startdate,'M/D/YYYY h:mm'))) AS distinct_lgh_startdate
;
Load * Inline
[
  %tractor, distinct_lgh_startdate, distinct_lgh_enddate
  679167, '6/5/2017 3:00' , '6/6/2017 12:00'
  679167, '6/5/2017 14:00' , '6/6/2017 16:00'
  679167, '6/7/2017 2:00' , '6/8/2017 3:00'
  679167, '6/6/2017 5:00' , '6/6/2017 7:00'
  679167, '6/6/2017 8:00' , '6/7/2017 22:00'
  111, '6/7/2017 3:00' , '6/9/2017 3:00'
  111, '6/7/2017 7:00' , '6/9/2017 7:00'
  111, '6/7/2017 12:00' , '6/8/2017 22:00'
]
;


ActivityDatesGrouped:
NoConcatenate
LOAD DISTINCT %tractor,
distinct_lgh_startdate,
        distinct_lgh_enddate
Resident ActivityDates
;
DROP TABLE ActivityDates
;

Any help is greatly appreciated.

Message was edited by: Justin Dallas

[Anonymous]

could you provide the corresponding app please?

[maxgro · Accepted solution]

Replace the resident load with

ActivityDatesGrouped: 

NoConcatenate 

LOAD DISTINCT

%tractor, 

     date(floor(distinct_lgh_startdate)) as distinct_lgh_startdate, 

    date(floor(distinct_lgh_enddate)) as distinct_lgh_enddate 

Resident ActivityDates 

; 

floor remove the time part of the field and the distinct works

[JustinDallas]

I would have thought casting it to a Date would have scrubbed the time part, but alas, I was wrong.