5 Replies Latest reply: Jul 15, 2011 6:46 AM by Luis Laura RSS

    pls check the script

      hi to all,

       

      In my file having an two inline tables. These two tables having same fields i.e; DateSelection.

       

      Here first table having Dates from 29-05-2011 10:59:59 to 31-05-2011 12:59:59.

       

      And Second table contains Dates from 01-06-2011 10:59:59 to 16-06-2011 10:09:59.

       

      But my requirement is when i select a "DateSelection" in listbox , it should get the dates from 29-05-2011 05:05:55 to 16-06-2011 06:06:55 ( TOTAL 19 DATES SHOULD VISIBLE IN A LISTBOX).

       

      How can i work on this.

       

      Below Script is what i used in Edit-Script.

       

      FirstTable:

       

      LOAD
      DatesField,
      Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
      SubField(DatesField, ' ', 1) AS CapDate


      Inline


      [DatesField
      29-05-2011 10:59:59
      30-05-2011 11:59:59
      31-05-2011 12:59:59];

       

       

      SecondTable:

       

      LOAD
      DatesField,
      Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
      SubField(DatesField, ' ', 1) AS CapDate


      INLINE [


      DatesField
      01-06-2011 10:59:59
      02-06-2011 11:59:59
      03-06-2011 12:59:59
      04-06-2011 1:40:59
      04-06-2011 1:50:59
      05-06-2011 2:59:59
      06-06-2011 3:59:59
      07-06-2011 4:59:59
      08-06-2011 5:59:59
      09-06-2011 6:59:59
      10-06-2011 2:59:59
      12-06-2011 7:39:59
      13-06-2011 8:29:59
      14-06-2011 9:19:59
      15-06-2011 10:09:59
      16-06-2011 10:02:59
      16-06-2011 10:09:59 ];

       

      I hope you understand what i am trying to acheive.

       

      Regards

      Venkat

        • pls check the script
          Johannes Sunden

          Hey Venkat,

           

          Not sure if you lost something in Copy/Paste but it looks close to correct:

           

          FirstTable:

           

          LOAD
          DatesField,
          Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
          SubField(DatesField, ' ', 1) AS CapDate;


          Load * Inline


          [DatesField
          29-05-2011 10:59:59
          30-05-2011 11:59:59
          31-05-2011 12:59:59];

           

           

          SecondTable:

           

          LOAD
          DatesField,
          Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
          SubField(DatesField, ' ', 1) AS CapDate;


          Load * INLINE [


          DatesField
          01-06-2011 10:59:59
          02-06-2011 11:59:59
          03-06-2011 12:59:59
          04-06-2011 1:40:59
          04-06-2011 1:50:59
          05-06-2011 2:59:59
          06-06-2011 3:59:59
          07-06-2011 4:59:59
          08-06-2011 5:59:59
          09-06-2011 6:59:59
          10-06-2011 2:59:59
          12-06-2011 7:39:59
          13-06-2011 8:29:59
          14-06-2011 9:19:59
          15-06-2011 10:09:59
          16-06-2011 10:02:59
          16-06-2011 10:09:59 ];

           

           

          Assuming the dates load correctly like this you should be good to go with the two tables being automatically concatenated into one table.

          • pls check the script
            Kaushik Solanki

            HI,

             

               Try this code.

             

            Sample:


            LOAD  * Inline
            [DatesField
            29-05-2011 10:59:59
            30-05-2011 11:59:59
            31-05-2011 12:59:59];

             

            Concatenate


            LOAD *

            INLINE [
            DatesField
            01-06-2011 10:59:59
            02-06-2011 11:59:59
            03-06-2011 12:59:59
            04-06-2011 1:40:59
            04-06-2011 1:50:59
            05-06-2011 2:59:59
            06-06-2011 3:59:59
            07-06-2011 4:59:59
            08-06-2011 5:59:59
            09-06-2011 6:59:59
            10-06-2011 2:59:59
            12-06-2011 7:39:59
            13-06-2011 8:29:59
            14-06-2011 9:19:59
            15-06-2011 10:09:59
            16-06-2011 10:02:59
            16-06-2011 10:09:59 ];

             

            Sample1:


            Load
            Date(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
            SubField(DatesField, ' ', 1) AS CapDate

            Resident Sample;

             

            Drop table Sample;

             

             

            Regards,

            Kaushik Solanki

              • Re: pls check the script

                Hi Kaushik,

                 

                what you given code is working for both INLINE tables.

                 

                But when i am trying to do by field from QVD and INLINE , it is not showing the Distinct Dates.

                 

                Pls see the below code:

                 

                Sample:
                LOAD
                CaptureDateTime  
                FROM
                [D:\RTQ\Version 0.1\Main1.qvd]
                (qvd);

                 

                Concatenate

                 

                LOAD * INLINE
                [CaptureDateTime
                06-05-2011 3:59:59
                07-05-2011 4:59:59
                08-05-2011 5:59:59
                09-05-2011 6:59:59
                10-05-2011 2:59:59
                12-05-2011 7:39:59
                13-05-2011 8:29:59
                14-05-2011 9:19:59
                15-05-2011 10:09:59
                16-05-2011 10:02:59
                16-05-2011 10:09:59 ];

                 

                Sample2:


                LOAD
                Date(CaptureDateTime, 'MM-DD-YYYY') as RepeatCaptureDate,
                SubField(CaptureDateTime, ' ', 1) as DistinctCaptureDate


                Resident Sample;

                DROP Table Sample;

                 

                The Result for DistinctCaptureDate is shown as below: i.e; from QVD it is not getting the DISTINCT dates.


                CaptureDist.PNG

                Regards

                Venkat

                  • Re: pls check the script
                    Miguel Angel Baeyens de Arce

                    Hello Venkat,

                     

                    The reason is that they are not actually distinct, since the Date() function you are using represents the date but keeps the subjacent numeric value for each record, so the dates contain times and thus are different. Rather, making a new field deleting the time part of the timestamp will do:

                     

                    Sample:
                    LOAD
                    CaptureDateTime AS OriginalDateTimeField,
                    Date(Floor(CaptureDateTime), 'DD-MM-YYYY') AS CaptureDateTime   
                    FROM
                    [D:\RTQ\Version 0.1\Main1.qvd]
                    (qvd);
                    

                     

                    You keep the original field should you need to use it later, but creating a listbox with CaptureDateTime will only show the date parts, so when you select "15-05-2011" all values in the original field with this date will be selected.

                     

                    Hope that help.s

                     

                    Miguel Angel Baeyens

                    BI Consultant

                    Comex Grupo Ibérica

                • Re: pls check the script
                  Luis Laura

                  Hi, No all is ok, pls see image adjust, Good luck, Luis

                   

                  Important:  I use date# for convert string to format date field