Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to get the next value of column.

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                  

1                     014-05-26 22:41:34                014-05-26 22:41:34                 2014-05-30 02:37:04

1                     2014-05-30 02:37:04              2014-05-30 02:37:04                2014-05-26 15:37:14

1                     2014-05-26 15:37:14                  2014-05-26 15:37:14              todays date

1 Solution

Accepted Solutions
MK_QSL
Not applicable

Re: How to get the next value of column.

  IF([defect id] = Previous([defect id]),Previous(time),Date(Today(),'YYYY-MM-DD') as EndDate,

25 Replies
MK_QSL
Not applicable

Re: How to get the next value of column.

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:

Load

  [defect id],

  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...

Not applicable

Re: How to get the next value of column.

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.

MK_QSL
Not applicable

Re: How to get the next value of column.

Check enclosed file..

Not applicable

Re: How to get the next value of column.

start date is coming correct but end date is not coming correct .

MK_QSL
Not applicable

Re: How to get the next value of column.

everything is matching with your question ...

Untitled.png

Not applicable

Re: How to get the next value of column.

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                    

1                     014-05-26 22:41:34                014-05-26 22:41:34                 2014-05-30 02:37:04

1                     2014-05-30 02:37:04              2014-05-30 02:37:04                2014-05-26 15:37:14

1                     2014-05-26 15:37:14                  2014-05-26 15:37:14              todays date

Not applicable

Re: How to get the next value of column.

defect id.JPG.jpg

MK_QSL
Not applicable

Re: How to get the next value of column.

Temp:

Load

  [defect id],

  TimeStamp(Timestamp#(time,'YYYY-MM-DD hh:mm:ss')) as time,

  Autonumber(RowNo()&[defect id]) as NO

Inline

[

  defect id, time  

  1,          2014-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

];

NoConcatenate

Final:

Load

  [defect id],

  time,

  time as StartDate,

  IF([defect id] = Previous([defect id]),Previous(time),TimeStamp(Today())) as EndDate,

  NO

Resident Temp

Order By [defect id] asc, NO Desc;

Drop Table Temp;

Not applicable

Re: How to get the next value of column.

now for end it is coming today's date and time is 12:00:00pm for all the rows