topic Re: Values duplicated/not unique for dimension in listbox in QlikView

Values duplicated/not unique for dimension in listbox

Thu, 03 Jul 2014 12:45:45 GMT

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!

Re: Values duplicated/not unique for dimension in listbox

Gysbert_Wassenaar — Thu, 03 Jul 2014 12:48:42 GMT

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 ...

Re: Values duplicated/not unique for dimension in listbox

MK_QSL — Thu, 03 Jul 2014 12:53:57 GMT

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

Re: Values duplicated/not unique for dimension in listbox

vardhancse — Thu, 03 Jul 2014 13:00:37 GMT

for any conversions we can use the function.

Date(Floor())

Year(Floor())

can you give some sample date.

Re: Values duplicated/not unique for dimension in listbox

Thu, 03 Jul 2014 13:00:57 GMT

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!

Re: Values duplicated/not unique for dimension in listbox

ashfaq_haseeb — Thu, 03 Jul 2014 13:05:34 GMT

Hi,

try below

Month(FullDate, 'MMM') as TextMonth,

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

Regards

ASHFAQ