After using distinct, duplication in date field

[#1]

Hi All

In the attached application though I wrote the script to take only the distinct values for 'date' field it is giving lots of duplicate values. Apart from the repeatation of dates there are also issues as given below.

Also if I select From Date and To Date it is giving one date lesser than what I have selected.

For example:

If I select 30/05/2011 in the From Date and 03/06/2011 in the End Date, It selects the date from 30/05/2011 to 02/06/2011

Apart from this there may be some other issues as well. But firstly would like to solve above 2 issues.

Can some one please help me out on this please.

[erichshiino]

You have duplicated values because you still have the timestamp behind your dates. I mean, you have, for example, for 26/05 at 12 and and 7:00. That's will you get two lines.

On your script, instead of date() , you can use daystart() to send your timestamps to date at midnight.

Hope it helps,

Erich

[#3]

Hi Erich

Thanks for your help! But do wants me to do like below?

Date(daystart(DATE(ImplStart+IterNo()-1))) as date

If the above code is correct requesting you to reply for one more problem which I have mentioned in my post.

Thanks in advance! Waiting for your reply...

[erichshiino]

I think this script should work.

I guess that your second problem is related to the first one, but you can correct it even without changes in your script.

Replace the search string of your selection action to this:

=if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<' & date(vEndDate+1,'$(DateFormat)'))

Regards,

Erich

[#5]

Hi Erich

Thanks for your help!

2nd problem has been resolved with the help of solution that you have provided. But I am still unable to 1st problem yet. After using the below code though the values are not getting repeated I am getting incorrect result in my report. Which is not yet included in the sample application which I have attached here. Problem is before using the code it displaying 15 rows in the report and after using your report it is displaying only 2 rows which is defiently incorrect. I think you will be able to understand once I share that report with you.

code]

Date(daystart(DATE(ImplStart+IterNo()-1))) as date

[/code]

Regards

Attitude

[rahulgupta]

Hey hi,

This would be your solution for the 1st issue.

Just use this in the back end if you want.

[#7]

Hi Rahul

Thanks for your help and effort. Could you please tell me where exactly I have to do the changes in the below script.

[code]

LOAD DISTINCT

DATE(ImplStart+IterNo()-1) AS date

RESIDENT Data

WHILE ImplStart+IterNo()-1<=ImplEnd;

[\code]

I did the changes in the below script but it is not giving the range of dates. It is displaying only those dates which are available. For example min(implstart) is 01/01/2011 and max(implend) is 15/01/2011 then it should display all the dates from 01/01/2011 to 15/01/2011. But currently if I do the below changes it is displaying only those dates which are available. It is not displaying range of dates. I think once I attach the sample application you can understand the problem easily.

Also I feel that if we do some changes above script then it will display the range of dates.

[code]

Load *,

     Week(date) as Week,

     Year(date) as Year,

     WeekName(date) as WeekName,

     MonthName(date) as MonthName,

     WeekDay(date) as WeekDay,

     Date(DayStart(date)) as DayStart    

     ;

[\code]

[rahulgupta]

Hi,

In case of specified MaxDate and MinDate, you can use the following lines of code in your script which will generate the range of dates within your maxdate and mindate defined.

/////////////////////Start Of the Calender Script////////////////////////////

set vdate=today();

TEMP:

LOAD

     num(min([Common Date])) AS MinDate,

     num(max([Common Date])) AS MaxDate

RESIDENT [COMMON INFO];

LET vMinDate = peek('MinDate', 0, 'TEMP');

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIslandTEMP:

LOAD

     date($(vMinDate) + rowno() - 1) AS Date,

     year($(vMinDate) + rowno() - 1) AS Year,

     month($(vMinDate) + rowno() - 1) AS Month,

     date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MonthYear,

     num(month($(vMinDate) + rowno() - 1)) AS Month#,

     year(yearname(date(($(vMinDate) + rowno() - 1), 'DD/MM/YYYY'),0,4)) AS [Fiscal Year]

AUTOGENERATE vMaxDate - vMinDate + 1;

/////End of the Script///

Hope this helps you.

[#9]

Hi Rahul

Thanks for your effort it is indeed giving the date of range(Displaying all the dates from 'implstart' to 'implend'). But when I use that in my report it is not displaying all the reports as I have dragged the dimension in to the top of the expression. So whatever the dates that are available in the dimension that only is getting displayed. That you will understand only when I attached the app.

But from and to calendar stopped working once I use the code that you have given. When you find time please look into it. As anyway I am looking into that.

Regards

Attitude

[#10]

Hi Erich and Rahul

Have attached the file for your reference. Hope it will be easy for you understand the issue which I am facing. If you have any doubts then please do let me know.