Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

Load Distinct not working as expected

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:

BadDupeCapture.PNG

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

1.png

View solution in original post

3 Replies
Anonymous
Not applicable

could you provide the corresponding app please?

maxgro
MVP
MVP

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

1.png

JustinDallas
Specialist III
Specialist III
Author

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