Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

Re: Comparing Timestamp

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

6 Replies
Not applicable

Re: Comparing Timestamp

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

Not applicable

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.

Not applicable

Re: Comparing Timestamp

Hi,

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

Regards

Not applicable

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.

Not applicable

Re: Comparing Timestamp

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

Not applicable

Re: Comparing Timestamp

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.

Community Browser