Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

Duplicate dates in date field

How can I avoid displaying duplicate dates in a date field? This field has been constructed from multiple different tables and the same date is displaying more than once both in a list box and a chart I have. Field is normalized to just show the date so time isn't causing the issue. Appreciate the help.

Tags (1)
3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Duplicate dates in date field

You probably have timestamps among your dates. 01/01/2015 00:00:00 and 01/01/2015 07:32 have the same date, but not same value. You can cut off the time part by using the floor function: date(floor(MyDate),'MM/DD/YYYY') as MyDate


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Duplicate dates in date field

I think my syntax is in correct for this

 

date(floor(WeekEnd(date#(Lookup('Delivery_Date', 'Delivery_ID', [Delivery No.], 'Delivery_Date'),'MM/DD/YYYY')), 0, 2),'MM/DD/YYYY') as

'ActionDate'

Do you know what i'm doing wrong here?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Duplicate dates in date field

Well, for one thing you can't use 0 as base for the floor function. Perhaps you meant 0 and 2 as parameters for the weekend function:

date(floor(WeekEnd(date#(Lookup('Delivery_Date', 'Delivery_ID', [Delivery No.], 'Delivery_Date'),'MM/DD/YYYY'), 0, 2)),'MM/DD/YYYY') as 'ActionDate'

Other than that, no idea. I have no idea in how many tables the ActionDate field exists.


talk is cheap, supply exceeds demand