15 Replies Latest reply: Jun 3, 2011 8:14 AM by Deepak Kurup RSS

    After using distinct, duplication in date field

      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.

        • After using distinct, duplication in date field
          Erich Shiino

          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

            • After using distinct, duplication in date field

              Hi Erich

               

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

               

              [code]

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

              [/code]

               

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

                • After using distinct, duplication in date field
                  Erich Shiino

                  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

                    • After using distinct, duplication in date field

                      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

                        • Re: After using distinct, duplication in date field
                          Rahul Gupta

                          Hey hi,

                           

                          This would be your solution for the 1st issue.

                          Just use this in the back end if you want.

                            • Re: After using distinct, duplication in date field

                              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]