topic Re: Comparing Timestamp in QlikView

Comparing Timestamp

Thu, 18 Oct 2012 06:03:25 GMT

Hi guys, I have a headache on this one, please help me if you can.

I have first table lets say

Header: No. Start

Row1: 1 2012-09-16 05:00:55

Row2: 1 2012-09-16 05:01:03

Row3: 1 2012-09-16 05:01:10

Row4: 1 2012-09-16 05:03:19

Row5: 1 2012-09-16 05:06:31

Row6: 2 2012-09-16 05:09:31

Second Table:

Header: No. End

Row1: 1 2012-09-16 05:01:00

Row2: 2 2012-09-16 05:16:00

I combine the two tables together and make this table

Header: No. Start End

Row1: 1 2012-09-16 05:00:55 2012-09-16 05:01:00

Row2: 1 2012-09-16 05:00:58 2012-09-16 05:01:00

Row3: 1 2012-09-16 05:01:10 2012-09-16 05:01:00

Row4: 1 2012-09-16 05:03:19 2012-09-16 05:01:00

Row5: 1 2012-09-16 05:06:31 2012-09-16 05:01:00

Row6: 2 2012-09-16 05:09:31 2012-09-16 05:16:00

As you can see I got alot of duplicate for No.1 with 'End' all the same value

But the record that I only want for No.1 is the very first occurance of 'Start' > 'End' for No.1, which is Row3.

I explain more:

Row1: 05:00:55 < 05:01:00 -----> dont want

Row2: 05:00:58 < 05:01:00 -----> dont want

Row3: 05:01:10 > 05:01:00 -----> Yes, and the first occurance -----> this is the record i want

Row4: 05:03:19 > 05:01:00 -----> Yes, but ignore the rest of the records

Any help will be appreciated.

I open to both script load and expression type.

Re: Comparing Timestamp

Thu, 18 Oct 2012 06:49:31 GMT

Hi,

Try:

LOAD header,

if(Time2>Time1,No.) as No.,

Time1,

Time2

and check supress when value is null on Dimenison tab for the field No.

Regards

Re: Comparing Timestamp

Thu, 18 Oct 2012 09:11:02 GMT

Hi techie, I realise I got the wrong idea. I just modified the question in more detail, please look through and assist me. Thanks alot.

Re: Comparing Timestamp

Thu, 18 Oct 2012 09:53:12 GMT

Hi,

why don't want u want Row 5, it also satisfies Start > End ?

Regards

Re: Comparing Timestamp

Thu, 18 Oct 2012 10:12:35 GMT

Because I only want the first occurance where START > END. Let's say No.1 I have 50 duplicates and the first occurance is at Row 3, I will want to ignore the rest from row 4 to row 50.

Re: Comparing Timestamp

Thu, 18 Oct 2012 10:25:44 GMT

Hi,

Use this :

LOAD if(Time2>[Time1] and No.<>previous(No.),No.) as No.,

header,

[Time1],

Time2

FROM the joined table // u can take resident load here so it becomes :

---------------------------------------------------------------------------------------------------------------------------

Table4:

LOAD if(Time2>[Time1] and No.<>previous(No.),No.) as No.,

header,

Time1,

Time2 resident Table3;

drop table Table3;

suppress null values in the chart for No.

Regards

Re: Comparing Timestamp

Mon, 22 Oct 2012 02:40:53 GMT

Hi techie,

sorry for the late reply, I was trying out the code you gave me the over the weekend and after modification, it works.

Thanks again for your help and some keywords.