Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
i have two column as time and defect id.The defect id has different time and date.i need to show the start date and end date of defect id.
if there are different defect id for each defect id i need to calculate the start and end date
defect id time
1 014-05-26 22:41:34
2 2014-05-28 05:59:11
1 2014-05-30 02:37:04
2 2014-05-26 00:07:52
1 2014-05-26 15:37:14
2 2014-05-26 22:41:34
3 2014-05-28 05:59:11
3 2014-05-30 02:37:04
etc
i need is:
defect id time start date end date
1 014-05-26 22:41:34 014-05-26 22:41:34 2014-05-30 02:37:04
2 2014-05-28 05:59:11 2014-05-28 05:59:11 2014-05-26 00:07:52
1 2014-05-30 02:37:04 2014-05-30 02:37:04 2014-05-26 15:37:14
2 2014-05-26 00:07:52 2014-05-26 00:07:52 2014-05-26 22:41:34
1 2014-05-26 15:37:14 2014-05-26 15:37:14 todays date
2 2014-05-26 22:41:34 2014-05-26 22:41:34 today date
3 2014-05-28 05:59:11 2014-05-28 05:59:11 2014-05-30 02:37:04
3 2014-05-30 02:37:04 2014-05-30 02:37:04 today date
if i will put filter on defect id say 1 is selected then it will show :
defect id time startdate end date
IF([defect id] = Previous([defect id]),Previous(time),Date(Today(),'YYYY-MM-DD') as EndDate,
View solution in original post
Something like below
Script
=================================
Temp:
Load
[defect id],
Timestamp(time) as time,
RowNo() as NO
Inline
[
defect id, time
1, 2014-05-26 00:07:52
1, 2014-05-26 07:15:32
1, 2014-05-26 22:41:34
];
NoConcatenate
Final:
time,
time as StartDate,
IF(NOT IsNull(Previous(time)),Previous(time),TimeStamp(Today())) as EndDate,
NO
Resident Temp
Order By NO Desc;
Drop Table Temp;
Drop Field NO;
==========================================
NOW Create a straight table or table box with your required fields...
it is not working . I have already loaded two columns from excel sheet.
I tried with your code but is showing error after concatenate.
if possible please attach qvw file for reference.
Check enclosed file..
start date is coming correct but end date is not coming correct .
everything is matching with your question ...
TimeStamp(Timestamp#(time,'YYYY-MM-DD hh:mm:ss')) as time,
Autonumber(RowNo()&[defect id]) as NO
2, 2014-05-28 05:59:11
1, 2014-05-30 02:37:04
2, 2014-05-26 00:07:52
1, 2014-05-26 15:37:14
2, 2014-05-26 22:41:34
3, 2014-05-28 05:59:11
3, 2014-05-30 02:37:04
IF([defect id] = Previous([defect id]),Previous(time),TimeStamp(Today())) as EndDate,
Order By [defect id] asc, NO Desc;
now for end it is coming today's date and time is 12:00:00pm for all the rows
