Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
Hi,
why don't want u want Row 5, it also satisfies Start > End ?
Regards
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.
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
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.