6 Replies Latest reply: Oct 21, 2012 10:40 PM by nelsontkx RSS

    Comparing Timestamp

      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
          Lav Jain

          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

              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
                  Lav Jain

                  Hi,

                   

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

                   

                   

                   

                  Regards

                    • Re: Comparing Timestamp

                      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
                          Lav Jain

                          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