Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
could you provide the corresponding app please?
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
I would have thought casting it to a Date would have scrubbed the time part, but alas, I was wrong.