Data Load - Only load certain hour from file

[varmekontrol]

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

[RicardodelaRosaQlik]

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

[Anonymous · Accepted solution]

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