topic Re: Date Extraction help. in QlikView

Date Extraction help.

lalitkgehlot89 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a table and Data is coming after every 10 second, due to large data application is getting slow.

I want to put the condition while extracting the data should be every 2 minutes only rather than every second?

data set record would be like this

01.01.01 12:00:00

01.01.01 12:00:10

01.01.01 12:00:20

01.01.01 12:00:30

01.01.01 12:00:40

01.01.01 12:00:50

01.01.01 12:01:00

01.01.01 12:01:10

01.01.01 12:01:20

01.01.01 12:01:30

01.01.01 12:01:40

01.01.01 12:01:50

01.01.01 12:02:00

01.01.01 12:02:10

01.01.01 12:02:20

01.01.01 12:02:30

01.01.01 12:02:40

01.01.01 12:02:50

01.01.01 12:03:00

01.01.01 12:03:10

01.01.01 12:03:20

01.01.01 12:03:30

01.01.01 12:03:40

01.01.01 12:03:50

01.01.01 12:04:00

01.01.01 12:04:10

01.01.01 12:04:20

01.01.01 12:04:30

01.01.01 12:04:40

01.01.01 12:04:50

01.01.01 12:05:00

01.01.01 12:05:10

01.01.01 12:05:20

01.01.01 12:05:30

01.01.01 12:05:40

01.01.01 12:05:50

01.01.01 12:06:00

01.01.01 12:06:10

01.01.01 12:06:20

01.01.01 12:06:30

01.01.01 12:06:40

01.01.01 12:06:50

01.01.01 12:07:00

01.01.01 12:07:10

01.01.01 12:07:20

01.01.01 12:07:30

01.01.01 12:07:40

01.01.01 12:07:50

output require like records would be

01.01.01 12:00:00

01.01.01 12:02:00

01.01.01 12:04:00

01.01.01 12:06:00

and so on.

Pls suggest

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 10:26:28 GMT

May be this:

Table:

LOAD DISTINCT Timestamp(Floor(TimeStamp, 2/(24*60))) as TimeStamp;

LOAD * Inline [

TimeStamp

01.01.01 12:00:00

01.01.01 12:00:10

01.01.01 12:00:20

01.01.01 12:00:30

01.01.01 12:00:40

01.01.01 12:00:50

01.01.01 12:01:00

01.01.01 12:01:10

01.01.01 12:01:20

01.01.01 12:01:30

01.01.01 12:01:40

01.01.01 12:01:50

01.01.01 12:02:00

01.01.01 12:02:10

01.01.01 12:02:20

01.01.01 12:02:30

01.01.01 12:02:40

01.01.01 12:02:50

01.01.01 12:03:00

01.01.01 12:03:10

01.01.01 12:03:20

01.01.01 12:03:30

01.01.01 12:03:40

01.01.01 12:03:50

01.01.01 12:04:00

01.01.01 12:04:10

01.01.01 12:04:20

01.01.01 12:04:30

01.01.01 12:04:40

01.01.01 12:04:50

01.01.01 12:05:00

01.01.01 12:05:10

01.01.01 12:05:20

01.01.01 12:05:30

01.01.01 12:05:40

01.01.01 12:05:50

01.01.01 12:06:00

01.01.01 12:06:10

01.01.01 12:06:20

01.01.01 12:06:30

01.01.01 12:06:40

01.01.01 12:06:50

01.01.01 12:07:00

01.01.01 12:07:10

01.01.01 12:07:20

01.01.01 12:07:30

01.01.01 12:07:40

01.01.01 12:07:50

];

Re: Date Extraction help.

Gysbert_Wassenaar — Wed, 18 May 2016 10:28:32 GMT

Split the timestamp into a date and a time:

LOAD

Date(Floor([My TimeStamp]),'DD.MM.YY') as [My Date],

Time(Floor(Frac([My TimeStamp]),1/720),'hh:mm') as [My Time],

...other fields...

FROM

....source...

;

Re: Date Extraction help.

Gysbert_Wassenaar — Wed, 18 May 2016 10:32:13 GMT

psst... every two minutes

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 10:34:12 GMT

Gysbert‌ what font size is that? I can hardly read that

Re: Date Extraction help.

Gysbert_Wassenaar — Wed, 18 May 2016 10:41:42 GMT

2 pt

4 pt

6 pt ◄ THIS ONE

8 pt

10 pt

Re: Date Extraction help.

lalitkgehlot89 — Wed, 18 May 2016 10:42:36 GMT

Thanks a lot Sunny & Gysbert,

But I am fetching data from SQL server.

Is there any option to write this with SQL where clause.

Regards,

Lalit Kmar

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 10:43:11 GMT

So you are deliberately making it difficult for me to read, aren't you?

Re: Date Extraction help.

sujeetsingh — Wed, 18 May 2016 10:43:22 GMT

Agree with Gysbert just brake the dates and time so that they will reduce the storage size.

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 10:43:59 GMT

I am sure SQL must have a way to Round()/Floor()/Ceil() things, but for that you would have to ask an SQL expert

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 10:46:12 GMT

May be explore here: Rounding time to nearest hourly quarter | Oracle Community

Re: Date Extraction help.

Gysbert_Wassenaar — Wed, 18 May 2016 10:46:25 GMT

I was trying to be subtle to give you a chance to ninja-edit 24*60 into 24*30

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 10:47:18 GMT

I used 2 in the numerator.

I used 2/(24* 60) = 1/(24*30)

Re: Date Extraction help.

Gysbert_Wassenaar — Wed, 18 May 2016 11:06:16 GMT

Bah, I should retire. I'm getting senile ...and blind

Re: Date Extraction help.

sunny_talwar — Wed, 18 May 2016 11:09:06 GMT

Retiring is not an option Gysbert‌, we still need your expertise here