Generated Min and Second Min Dates based on other values

[Sai33]

Hi all,

I've my source data in the below format

ID	Exit Code	Entry Code	Date
A123	CO	CO	01.09.2020
A123	CO	CO	03.09.2020
A123	CO	CO	05.09.2020
A123	CO		15.09.2020
A123		CO	23.11.2020
A123	CO	CO	26.11.2020
A123	CO		05.12.2020
A123		CO	06.12.2020
A123	CO	CO	07.12.2020
A123	CO	CO	08.12.2020

I'm looking to generate 4 Dates for the ID -

Entry - The first Date

Exit - The first Date where the Entry Code is empty

Re-Entry - The first Date after the Date where Exit Code is empty

Re-Exit - The Second Date where the Entry Code is empty

my expected output would be as below

ID	Entry	Exit	Re-Entry	Re-Exit
A123	01.09.2020	15.09.2020	23.11.2020	05.12.2020

Any pointers here would be really helpful.

Regards

Sai

[edwin]

look at attached if this is what you need.  it determines the min date per ID, and then to get the next, the same formula becomes a criteria where dates are greater than that

[Kushal_Chawda]

@Sai33  do you want to do it in script or front end?

[Sai33]

Hi, @Kushal_Chawda  in script

[Sai33]

Hi all,

Any quick suggestions here, looking to build this logic in Script.

Thanks.

[Barttonn]

The Excel MIN function returns the smallest numeric value in a range of values. This generates an array of TRUE / FALSE values, where TRUE corresponds to rows the MIN and TODAY functions to find the "next date" based on the date today. The formulas shown in the example all use the AVERAGE function with a .

[MayilVahanan · Accepted solution]

Hi @Sai33 

Try like below

One of method to achieve the result.

Temp:
Load *, if(Len(Trim([Exit Code]))=0 and ex = 1, Date2) as Re_Entry, if(Len(Trim([Entry Code]))=0 and et = 2, Date2) as Re_Exit, if(Len(Trim([Entry Code]))=0 and et = 1, Date2) as Exit;
LOAD *, if(ID = Peek(ID), if(Len(Trim([Exit Code]))=0, Peek('ex')+1, alt(Peek('ex'),0)), 0) as ex,
if(ID = Peek(ID), if(Len(Trim([Entry Code]))=0, Peek('et')+1, alt(Peek('et'),0)),0) as et INLINE [
ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];

Load ID, Date(Min(Date2)) as Entry, Date(Min(Exit)) as Exit, Date(Min(Re_Entry)) as Re_Entry, Date(Min(Re_Exit)) as Re_Exit Resident Temp
group by ID;

[Sai33]

Hi @MayilVahanan 

Your code also works perfectly, but i've found a issue in my data

ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020

A123,CO,,15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020

A123,Co,,05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];

For each empty Entry Code- i've two entreis in my DB. This is leading the Exit and Re-Exit dates to always be the same. 

I've updated my table in the comment here. Can you please help me out here.

Thanks

Sai

[MayilVahanan]

Hi @Sai33 

Try like below

Temp:

LOAD Distinct * INLINE [
ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020
A123, CO, , 15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];

Temp1:
Load *, if(Len(Trim([Exit Code]))=0 and ex = 1, Date2) as Re_Entry, if(Len(Trim([Entry Code]))=0 and et = 2, Date2) as Re_Exit, if(Len(Trim([Entry Code]))=0 and et = 1, Date2) as Exit;
Load *, if(ID = Peek(ID), if(Len(Trim([Exit Code]))=0, Peek('ex')+1, alt(Peek('ex'),0)), 0) as ex,
if(ID = Peek(ID), if(Len(Trim([Entry Code]))=0, Peek('et')+1, alt(Peek('et'),0)),0) as et resident Temp;

Load ID, Date(Min(Date2)) as Entry, Date(Min(Exit)) as Exit, Date(Min(Re_Entry)) as Re_Entry, Date(Min(Re_Exit)) as Re_Exit Resident Temp1
group by ID;

DROP Table Temp, Temp1;

[blacksmith15590]

walmartone wire

myaccountaccess.space

One Vanilla gifts

OneCognizant login