2 Replies Latest reply: Sep 22, 2017 7:47 AM by Javier Millas RSS

    Data Load - Only load certain hour from file

    Daniel Sørensen

      HI

       

      Could someone explain to me how to only load rows for a certain timestamp?

      I have over 10k files with 900-100 rows because there is 24 rows pr. day and i am only interested in one of those rows.

       

      This is my script for now.


      LOAD

          "#serial-number",

          "device-identification" as [Målernr.],

        created,

          "volume,m3,inst-value,0,0,0"

      FROM [lib://Blåkildevej - 1108 - Water/*.csv]

      (txt, codepage is 28591, embedded labels, delimiter is ';', msq)

       

      The 'created' is my timestamp and looks like this:

      18-02-2017  00:00:00

      18-02-2017  01:00:00

      18-02-2017  02:00:00

      18-02-2017  03:00:00

       

      Question -> What should I add to my load script, so that I only load the hour-timestamp 00:00 for every day, and exclude all the other hours....

        • Re: Data Load - Only load certain hour from file
          De La Rosa Ricardo

          Hi,

          you only need to add a "where" clausule like this:

           

          LOAD

              "#serial-number",

              "device-identification" as [Målernr.],

            created,

              "volume,m3,inst-value,0,0,0"

          FROM [lib://Blåkildevej - 1108 - Water/*.csv]

          (txt, codepage is 28591, embedded labels, delimiter is ';', msq)

          where [created] like '*00:00:00';

           

          With that your script will only load rows with the created column with a value that would end with 00:00:00.

          I hope that helps you get what you want.

           

          Regards,

          Ricardo

          • Re: Data Load - Only load certain hour from file
            Javier Millas

            Hi,

             

            one solution would be to select the registers meeting your condition (hour =0) including the condition with the function "hour". Try using the following script:

             

            AllData:

            LOAD

                "#serial-number",

                "device-identification" as [Målernr.],

              created,

                "volume,m3,inst-value,0,0,0"

            FROM [lib://Blåkildevej - 1108 - Water/*.csv]

            (txt, codepage is 28591, embedded labels, delimiter is ';', msq) where hour (created)= 0;

             

            I  hope this helps,

            Cheers,

             

            Javier