Comparing Timestamp

[#1]

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.

[#2]

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

[#3]

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.

[#4]

Hi,

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

Regards

[#5]

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.

[#6 · Accepted solution]

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

[#7]

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.