topic Re: How to remove duplicate dates in my calender? in QlikView

How to remove duplicate dates in my calender?

durgabhavani — Wed, 20 May 2015 06:24:07 GMT

Hi All,

I have one field "Date". It has following dates in my field.

Date
4/5/2015
4/5/2015
6/12/2015
7/25/2015
1/22/2015
2/14/2015
6/12/2015
6/15/2015
9/15/2015
6/12/2015
7/25/2015
1/22/2015
2/14/2015
6/12/2015
6/15/2015
9/15/2015
2/17/2015
6/12/2014
7/11/2014
8/15/2014
5/18/2014

I am using this date field in my calender object. Now i want to remove duplicate dates in my calender object. Please advice.

Thanks,

Durga

Re: How to remove duplicate dates in my calender?

danieloberbilli — Wed, 20 May 2015 06:28:11 GMT

Load Distinct ... should do the job.

I dont no how your data/calendar looks like - so just as an example to show a possible approach: you could load a new table with the distinct dates and join the calendar:

DistinctDates:

LOAD Distinct

Date

resident Calendar_tmp;

left join LOAD * resident Calendar_tmp;

Drop table Calendar_tmp;

Re: How to remove duplicate dates in my calender?

durgabhavani — Wed, 20 May 2015 07:21:54 GMT

Thanks for reply daniel!

I have "date" field in my table.. just i am using that field in my calender object. but when i am selecting date in calender it is not displaying the selected date in calender object due to duplicate dates (multiple same dates as mentioned above). so i am planning to remove duplicate dates in my calender object.

So please advice how to remove duplicate dates in my calender object.

Thanks,

Durga

Re: How to remove duplicate dates in my calender?

danieloberbilli — Wed, 20 May 2015 07:26:48 GMT

could you provide a screenshot? I am not sure: when you say 'object' are you talking about a straight (or pivot) table object on the dashboard? It depends what you got as dimension and expressions

Re: How to remove duplicate dates in my calender?

durgabhavani — Wed, 20 May 2015 08:47:00 GMT

Daniel,

Please check the two screenshots. when i select date (1/11/2012) if it is only one date then it is displaying in calender object. if selected date (3/17/2015) is multiple then it is not displaying in calender. So i want to remove the duplicate dates (i.e i want to make the 3/17/2015 as distinct). How can i do this.

I took field (Created_date) from my db table in calender object.

Hope this helps.

Re: How to remove duplicate dates in my calender?

danieloberbilli — Wed, 20 May 2015 08:57:57 GMT

You dont have dates - you have timestamps in your field. Try to transform your timestamps into dates in the script e.g. like

date(floor(CREATED_DATE),'DD/MM/YYYY') as RealDate

Re: How to remove duplicate dates in my calender?

qlikviewwizard — Wed, 20 May 2015 09:08:03 GMT

Hi Use the below format for the date.

SET DateFormat='DD-MMM-YYYY';

Date:

load * inline [

DATEFILED

4/5/2015

6/12/2015

7/25/2015

1/22/2015

2/14/2015

6/12/2015

6/15/2015

9/15/2015

6/12/2015

7/25/2015

1/22/2015

2/14/2015

6/12/2015

6/15/2015

9/15/2015

2/17/2015

6/12/2014

7/11/2014

8/15/2014

5/18/2014

];

Output:

Re: How to remove duplicate dates in my calender?

durgabhavani — Wed, 20 May 2015 09:08:48 GMT

You rocks Daniel!

its Working!! I am getting correct output now.

Thanks much for completing my post.

Re: How to remove duplicate dates in my calender?

Wed, 20 May 2015 09:44:22 GMT

durga g!

try this

date(floor(YOUR DATE FIELD),'DD/MM/YYYY') as RealDate

Re: How to remove duplicate dates in my calender?

durgabhavani — Wed, 20 May 2015 10:23:15 GMT

Atlast,

Can anyone tell me how to restrict my 20 years calender to only 3 years.

i.e my calender object is showing last 20 years, but i want to display only last three years.

Thanks,

Durga

Re: How to remove duplicate dates in my calender?

danieloberbilli — Wed, 20 May 2015 10:58:37 GMT

I think this does not work in the calendar box object.

You could create another field like

if(RealDate>='01/01/2012', RealDate, Null()) as RealDateForCalendarObj

Re: How to remove duplicate dates in my calender?

durgabhavani — Wed, 20 May 2015 11:59:49 GMT

I wrote this condition in calender expression. it is working..

Thanks,

Durga