Values duplicated/not unique for dimension in listbox

[#1]

I have attached an image of what I am seeing - after creating some new import scripts with some date/time functions in them, I am seeing my new data being treated as if every individual raw record has a unique value for every date field.  For example, I created a listbox on Year and each year is duplicated thousands of times in the listbox. Selecting the first instance of '2014' only returns a single record in the database, instead of all 2014 data.

Any ideas what could cause this? Thanks!

[Gysbert_Wassenaar]

You're probably using a date or timestamp field and only changed the display format to year. If you want a Year field then create that in the script:

Load MyDate,

     Year(MyDate) as MyYear,

     Month(MyDate) as MyMonth,

     Week(MyDate) as MyWeek

etc...

From ...

[MK_QSL]

Provide sample date or year field data your are using... looks like your date is timestamp..

[vardhancse]

for any conversions we can use the function.

Date(Floor())

Year(Floor())

can you give some sample date.

[#5]

Yes, it started as Unix Timestamp.

In one step, I applied:

ConvertToLocalTime(timestamp((@4 / 86400) + (2 + date#('1/1/1970') - date#('1/1/1900'))), 'GMT-05:00') as FullDate

Then in the next step, I did all these:

date(FullDate, 'YYYY-MM-DD') as Date,

date(FullDate, 'YYYY') as Year,

date(FullDate, 'MM') as Month,

date(FullDate, 'DD') as Day,

	date(date(FullDate, 'MM'), 'MMM') as TextMonth,
	date(FullDate, 'YYYYMM') as YearMonthField,
	time(FullDate, 'hh:mm:ss') as Time

I have changed it now to this:

Year(FullDate) as Year,

Month(FullDate) as Month,

Day(FullDate) as Day,

	time(FullDate, 'hh:mm:ss') as Time,
	Year(FullDate)&Month(FullDate) as YearMonthField,
	date(date(FullDate, 'MM'), 'MMM') as TextMonth,

date(FullDate, 'YYYY-MM-DD') as Date,   

This seems to have fixed the first 5 fields. But I need to determine which functions to switch to for the last 2 (TextMonth and Date) now.

Thanks to all for the help so far!

[ashfaq_haseeb]

Hi,

try below

Month(FullDate, 'MMM') as TextMonth,

date(floor(FullDate), 'YYYY-MM-DD') as Date,  

Regards

ASHFAQ