Next Min(Date)

[#1]

Test:

LOAD * INLINE [

    Min, ID, Max

   01/13/2016, 1, 01/14/2016

    01/17/2016, 1, 01/18/2016

    01/19/2016, 1, 01/21/2016

    01/14/2016, 2, 01/15/2016

    01/20/2016, 2, 01/21/2016

    01/26/2016, 2, 01/27/2016

];

Hi Guys, Please look at the above table. I have an ID with a Min date and Max date. I would like to add a field with the Next Min Date.

So ID 1 would have a Min Date of 01/13/2016, a Max Date of 01/14/2016 and a Next Min date of 01/17/2016. How can I achieve this in the back end?

Kind Regards,

[sunny_talwar]

May be like this:

Test:

LOAD * INLINE [

    Min, ID, Max

  01/13/2016, 1, 01/14/2016

    01/17/2016, 1, 01/18/2016

    01/19/2016, 1, 01/21/2016

    01/14/2016, 2, 01/15/2016

    01/20/2016, 2, 01/21/2016

    01/26/2016, 2, 01/27/2016

];

Left Join (Test)

LOAD ID,

  Date(Min(Min, 2)) as [Next Min Date]

Resident Test

Group By ID;

To get this

[krishnacbe]

Hi,

Use the formula below in the Chart expression to get the Min Row.

Aggr(MinString(Min),ID)

Hope the attached QVW fix the issue.

[#4]

Hi Sunny!

Thanks for your reply, but this only gives the second record of each ID instead of next min date for that record. In other words 01/17/2016 should have a next min date of 01/19/2016.

Min            ID     Max               Next Min Date

01/13/2016  1      01/14/2016     01/17/2016

01/17/2016  1      01/18/2016     01/19/2016

01/19/2016  1      01/21/2016     Null

01/14/2016  2      01/15/2016     01/20/2016

and so on.

Thanks

[#5]

I managed to figure it out, thanks sunny!

Like this.

Test:

TmpTable:

LOAD * INLINE [Min, ID, Max

01/13/2016, 1, 01/14/2016

01/17/2016, 1, 01/18/2016

01/19/2016, 1, 01/21/2016

01/14/2016, 2, 01/15/2016

01/20/2016, 2, 01/21/2016

01/26/2016, 2, 01/27/2016];

MyTable:

LOAD *,

          if(previous(ID)=ID,Previous(Min),null()) as NEXT_MIN

Resident TmpTable Order by ID,Min Asc;

Drop Table TmpTable;

[sunny_talwar]

I gotch ya. I was not sure what the required output was so it was mostly a guess work. But next time when you ask, it would be helpful to know what your expected output is so that we can help you better

Best,

Sunny