Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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.
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
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;
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