topic Next Min(Date) in QlikView

Next Min(Date)

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Next Min(Date)

sunny_talwar — Mon, 07 Nov 2016 16:51:21 GMT

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

Re: Next Min(Date)

krishnacbe — Mon, 07 Nov 2016 16:53:19 GMT

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.

Re: Next Min(Date)

Mon, 07 Nov 2016 22:57:56 GMT

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

Re: Next Min(Date)

Mon, 07 Nov 2016 23:03:02 GMT

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;

Re: Next Min(Date)

sunny_talwar — Tue, 08 Nov 2016 01:33:34 GMT

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