Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
JustinDallas
Not applicable

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
Not applicable

Re: Load Distinct not working as expected

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

3 Replies
roharoha
Not applicable

Re: Load Distinct not working as expected

could you provide the corresponding app please?

maxgro
Not applicable

Re: Load Distinct not working as expected

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
Not applicable

Re: Load Distinct not working as expected

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