Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

Duplicate Dates in Date Field - Floor not working

I have a Date I am pulling in from a qvd formatted YYYYMMDD. I have tried everything to reformat and get the date to be just YYYYMM format. No matter the formatting I try, the closest I can get is the correct format, but then duplicates of dates like the Day is still attached. I tried Floor to remove it and group them again, but to no avail.

 

This is the script currently getting me the closest, but still duplicates.

Date(Floor(DATE#(DATEFIELD,'YYYYMMDD')),'YYYYMM') AS NEWDATE,

Capture.PNG

 

1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

The duplicate dates (2016-04) probably come from having records from 3 different days in April.  You could "select distinct NEWDATE from resident "table name" to eliminate the duplicates.

But if you are not using a calendar in your app, I would suggest that a calendar will make your life easier.

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

The duplicate dates (2016-04) probably come from having records from 3 different days in April.  You could "select distinct NEWDATE from resident "table name" to eliminate the duplicates.

But if you are not using a calendar in your app, I would suggest that a calendar will make your life easier.

Vegar
MVP
MVP

It's just as previous speaker mentioned, you get multiple days with the same text formatting.

You could try this:
Date(monthname(DATE#(DATEFIELD,'YYYYMMDD')),'YYYYMM') AS NEWDATE,

Or even simpler:
monthname(DATE#(DATEFIELD,'YYYYMMDD')) AS NEWDATE,
florentina_doga
Partner - Creator III
Partner - Creator III

use distinct