How to remove duplicate dates in my calender?

[durgabhavani]

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

[danieloberbilli]

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;

[durgabhavani]

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

[danieloberbilli]

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

[durgabhavani]

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.

[danieloberbilli · Accepted solution]

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

[qlikviewwizard]

Hi Use the below format for the date.

SET DateFormat='DD-MMM-YYYY';

Date:

load * inline [

DATEFILED

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

];

Output:

[durgabhavani]

You rocks Daniel!

its Working!! I am getting correct output now.

Thanks much for completing my post.

[#9]

durga g!

try this

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

[durgabhavani]

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